[RESOLVED] SQL: Secondary Sorting

[QuinnJohns]

I've got an issue right now, where I'm using two different tables, that share a primary key.
What I currently have is,

a Table called Application, which holds:

ApplicationID: (primary key)
Name:
Description

a table called Favorite, which holds an ID, application ID (foreign), computer login id.

ID:
ApplicationID: (foreign key)
Enumber: (( Environment.Username))

-- what I am doing is on an ASP.NET page, upon click I'm insert/deleting/selecting content from these two tables - I can add just fine, delete just fine, but I'd like to generate a query SORTING my gridview such that:

my favorites are on the top, and the nonfavorites are on bottom, and sort based on name in ADDITION.

so you could imagine....
"starred" items being on top, sorted by name, and right below, non-star sorted by name.

I've tried an assortment of joins, but can't seem to get it right.

SELECT     Favorite.ID, Application.Name
FROM        Application LEFT OUTER JOIN Favorite ON (what should go here??)
WHERE     Favorite.Enumber = '<whatever user>'
ORDER BY ????, ????

Thanks for any help I can get!

[ideru]

basically you want to rank your data based on the number of access right..

This might be a bit of help:
http://weblogs.sqlteam.com/jeffs/arc.../28/60146.aspx

[QuinnJohns]

Well, essentially, I want to have the selections that have a favorite in the favorite table on the top, followed by the rest, and each of those two groups will be sorted by application name.

I hope that makes sense.

example:

table: favorite
---
id      Enumber            ApplicationID
1       quinnjohns        32
2       quinnjohns        48

table: application
--
ID      Name                ...
1       Alpha1               .
2       Bravo1              .
32     Epsilon1            .
48     Charlie1            .

----
Results to display on Grid:
---

Charlie1 <-- * Favorite
Epsilon1 <-- * Favorite
Alpha1
Bravo1

Would that ranking post help with this?

[DataMiser]

Now sure how you would want to sort since you are only selecting 2 fields. I would think by Favorite.ID and since I would assume that this is a unique key and there is only one other field then there is not much point in a secondary sort.

SELECT     Favorite.ID, Application.Name
FROM        Application LEFT OUTER JOIN Favorite ON Favorite.ApplicationID=Application.ID
WHERE     Favorite.Enumber = '<whatever user>'
ORDER BY Favorite.ID

[QuinnJohns]

Now sure how you would want to sort since you are only selecting 2 fields. I would think by Favorite.ID and since I would assume that this is a unique key and there is only one other field then there is not much point in a secondary sort.

SELECT     Favorite.ID, Application.Name
FROM        Application LEFT OUTER JOIN Favorite ON Favorite.ApplicationID=Application.ID
WHERE     Favorite.Enumber = '<whatever user>'
ORDER BY Favorite.ID

Favorite.ID and Application.Name

[DataMiser]

Favorite.ID and Application.Name

I guess I missed something.

order by Favorite.id, Application.Name

[QuinnJohns]

SELECT Application.*, 
Rank() OVER (PARTITION  BY Favorite.ApplicationID ORDER BY Application.Name ASC) as 'Ranking' 
FROM Application LEFT OUTER JOIN Favorite ON Application.ID = Favorite.ApplicationID

They are grouping correctly, however the Favorited ones are showing up on Bottom, and aren't ordering per the "Application.Name" like it was asked, matter of fact, its not ordering at all. If I do an order by afterwards, it negates the Ranking.

Any suggestions?

[QuinnJohns]

SELECT Application.ID, Application.Name, Favorite.ID AS Expr1, Favorite.Enumber, Favorite.ApplicationID 
FROM Application LEFT OUTER JOIN Favorite ON Application.ID = Favorite.ApplicationID 
ORDER BY (CASE WHEN Favorite.ApplicationID IS NULL THEN 1 ELSE 0 END), Application.Name

Got me the solution I needed. Wow, I can't believe I figured that out.