KARL said:
You will need two fields. One call TopSort and make it a number field.
Default to 0. For those Family items use 1 in the field.
Are you advising the OP to create a redundant second column for their
one-column table? I would not recommend this. I would instead recommend
they determine the sort order based on the data (assuming the 'bottom'
categories also sort alphabetically among themselves e.g.
CREATE TABLE MediaCategories (
media_category_name VARCHAR(30) NOT NULL PRIMARY KEY
)
;
INSERT INTO MediaCategories VALUES ('Films')
;
INSERT INTO MediaCategories VALUES ('Family films')
;
INSERT INTO MediaCategories VALUES ('Books')
;
INSERT INTO MediaCategories VALUES ('Family DVDs')
;
INSERT INTO MediaCategories VALUES ('Plays')
;
INSERT INTO MediaCategories VALUES ('DVDs')
;
SELECT media_category_name, 1 AS category_sort_order
FROM MediaCategories
WHERE media_category_name IN ('Family DVDs', 'Family films')
UNION ALL
SELECT media_category_name, 0 AS category_sort_order
FROM MediaCategories
WHERE media_category_name NOT IN ('Family DVDs', 'Family films')
ORDER BY 2, 1;
The disadvantage here is that the 'bottom' (compulsory?) categories are
hard-coded into the SQL.
An alternative approach would be to use a second table to model the
compulsory categories. KARL could be assuming that compulsory
categories do not sort alphabetically among themselves, rather have an
explicit sort order; adding a column to this table would avoid the
redundancy of his one-table two-column solution:
CREATE TABLE CompulsoryMediaCategories (
media_category_name VARCHAR(30) NOT NULL PRIMARY KEY
REFERENCES MediaCategories (media_category_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
category_sort_order INTEGER NOT NULL UNIQUE,
CONSTRAINT compulsory_media_category_sort_order__positive
CHECK (category_sort_order > 0)
)
;
INSERT INTO CompulsoryMediaCategories VALUES ('Family films', 2)
;
INSERT INTO CompulsoryMediaCategories VALUES ('Family DVDs', 1)
;
SELECT M1.media_category_name, IIF(C1.category_sort_order IS NULL, 0,
C1.category_sort_order) AS category_sort_order
FROM MediaCategories AS M1
LEFT JOIN CompulsoryMediaCategories AS C1
ON M1.media_category_name = C1.media_category_name
ORDER BY IIF(C1.category_sort_order IS NULL, 0,
C1.category_sort_order), M1.media_category_name
;
Note in the above query it is good practice to expose the sort order to
the front end application (it may even be more efficient to sort the
resultset in the middleware e.g. adodb.recordset.sort).
Here's some VBA code to recreate the above example:
Sub testKARL()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"
With .ActiveConnection
' Dummy one-row temp table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"
.Execute _
"CREATE TABLE MediaCategories ( media_category_name" & _
" VARCHAR(30) NOT NULL PRIMARY KEY ) ; "
.Execute _
"INSERT INTO MediaCategories (media_category_name)" & _
" SELECT DT1.media_category_name FROM ( SELECT" & _
" 'Films' AS media_category_name FROM DropMe" & _
" UNION ALL SELECT 'Family films' AS media_category_name" & _
" FROM DropMe UNION ALL SELECT 'Books' AS" & _
" media_category_name FROM DropMe UNION ALL" & _
" SELECT 'Family DVDs' AS media_category_name" & _
" FROM DropMe UNION ALL SELECT 'Plays' AS" & _
" media_category_name FROM DropMe UNION ALL" & _
" SELECT 'DVDs' AS media_category_name FROM" & _
" DropMe ) AS DT1;"
' Drop temp table
.Execute _
"DROP TABLE DropMe;"
.Execute _
"CREATE TABLE CompulsoryMediaCategories (" & _
" media_category_name VARCHAR(30) NOT NULL" & _
" PRIMARY KEY REFERENCES MediaCategories" & _
" (media_category_name) ON DELETE CASCADE" & _
" ON UPDATE CASCADE, category_sort_order" & _
" INTEGER NOT NULL UNIQUE, CONSTRAINT
compulsory_media_category_sort_order__positive" & _
" CHECK (category_sort_order > 0) );"
.Execute _
"INSERT INTO CompulsoryMediaCategories VALUES" & _
" ('Family films', 2);"
.Execute _
"INSERT INTO CompulsoryMediaCategories VALUES" & _
" ('Family DVDs', 1);"
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
With rs
.CursorLocation = 3 ' client side
.ActiveConnection = cat.ActiveConnection
.Source = _
"SELECT M1.media_category_name, IIF(C1.category_sort_order" & _
" IS NULL, 0, C1.category_sort_order) AS" & _
" category_sort_order FROM MediaCategories" & _
" AS M1 LEFT JOIN CompulsoryMediaCategories" & _
" AS C1 ON M1.media_category_name = C1.media_category_name;"
.Open
.Sort = "category_sort_order, media_category_name"
MsgBox .GetString(2, , vbTab & vbTab) ' clip string
.Close
End With
End With
Set .ActiveConnection = Nothing
End With
End Sub
Jamie.
--
