grouping and ordering in OUTER JOIN

Here's table 1:
Fans
FanID, username
Table 2:
Photos
PhotoID, FanID, photofilename, defaultPhoto (true or false)
Not all records in Fans will have a corresponding record in Photos.
I'm doing an outer join that gets ALL the Fan records, and if they have
a record in Photos, and displays the Photofilename. If it does not
have a Photo record, it displays NULL.
Here's the query:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
Where DefaultPhoto = 'True' or photofilename is NULL
This results in a recordset like this:
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
hollywood 16 hollywood_1.jpg
Ed Shiv 71 (NULL)
monte 18 (NULL)
abm 19 abm_1.jpg
and2 20 (NULL)
and 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
dave 24 dave_2.jpg
I need to organize these results with the records WITH a Photofilename
value FIRST and then all those with NULL in the Photofilename next.
I will then also like to order by NEWID() (randomize within these
groups)
Something like this:
hollywood 16 hollywood_1.jpg
abm 19 abm_1.jpg
dave 24 dave_2.jpg
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
Ed Shiv 71 (NULL)
monte 18 (NULL)
and2 20 (NULL)
andl 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
I've tried to use GROUP BY but I get an error if I don't include ALL
the fields from the select.
I thought I'd be able to say GROUP BY Photofilename ORDER BY NEWID()
but it's not working.
Any suggestions?You don't need a GROUP BY and I don't believe you need that WHERE clause.
(Please post your DDL.) That said, try:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
ORDER BY
case when Photos.FanID is null then 0 else 1 end
, newid()
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146606774.674581.3760@.g10g2000cwb.googlegroups.com...
Here's table 1:
Fans
FanID, username
Table 2:
Photos
PhotoID, FanID, photofilename, defaultPhoto (true or false)
Not all records in Fans will have a corresponding record in Photos.
I'm doing an outer join that gets ALL the Fan records, and if they have
a record in Photos, and displays the Photofilename. If it does not
have a Photo record, it displays NULL.
Here's the query:
SELECT username, fans.FanID, photofilename FROM Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID
Where DefaultPhoto = 'True' or photofilename is NULL
This results in a recordset like this:
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
hollywood 16 hollywood_1.jpg
Ed Shiv 71 (NULL)
monte 18 (NULL)
abm 19 abm_1.jpg
and2 20 (NULL)
and 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
dave 24 dave_2.jpg
I need to organize these results with the records WITH a Photofilename
value FIRST and then all those with NULL in the Photofilename next.
I will then also like to order by NEWID() (randomize within these
groups)
Something like this:
hollywood 16 hollywood_1.jpg
abm 19 abm_1.jpg
dave 24 dave_2.jpg
25grove 65 (NULL)
blahblah 68 (NULL)
v 70 (NULL)
Ed Shiv 71 (NULL)
monte 18 (NULL)
and2 20 (NULL)
andl 21 (NULL)
Schiavo 72 (NULL)
username 23 (NULL)
I've tried to use GROUP BY but I get an error if I don't include ALL
the fields from the select.
I thought I'd be able to say GROUP BY Photofilename ORDER BY NEWID()
but it's not working.
Any suggestions?|||Thank you! Looks like that's doing the trick!|||After all this...is there a way to retrieve a RANGE of these?
For instance, records 100-115...
Since I'm pulling down 13000 records, it's causing a timeout
Thanks,
Andy|||Could you give us an example? It's not clear what you want.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146691570.341007.131600@.y43g2000cwc.googlegroups.com...
After all this...is there a way to retrieve a RANGE of these?
For instance, records 100-115...
Since I'm pulling down 13000 records, it's causing a timeout
Thanks,
Andy|||I just want to retrieve 100 or so records...or a specific range of
records from this query:
SELECT
(fans. Fanid*(1+datepart(s,getDate()))*(1+datep
art(ms,getDate())))%1000
sortid, username, fans.FanID, PhotoID from Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID where defaultphoto
= 'true' or photoID is NULL
ORDER BY
case when Photos.FanID is null then 1 else 0 end, sortid
Also, should this query be taking 20 seconds to execute?
The tables it's pulling from have 14,000 (Fans) and 20,000 (Photos)
records in them.|||The first thing I'd do is look at your indexing. Does Photos have an index
on FanID?
As for limiting the number of rows, you could use SELECT TOP 100, for
example, to get 100 rows. Be sure to use an appropriate ORDER BY clause.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<andymilk@.gmail.com> wrote in message
news:1146750558.397604.245250@.i39g2000cwa.googlegroups.com...
I just want to retrieve 100 or so records...or a specific range of
records from this query:
SELECT
(fans. Fanid*(1+datepart(s,getDate()))*(1+datep
art(ms,getDate())))%1000
sortid, username, fans.FanID, PhotoID from Fans
LEFT OUTER JOIN Photos on fans.FanID = photos.FanID where defaultphoto
= 'true' or photoID is NULL
ORDER BY
case when Photos.FanID is null then 1 else 0 end, sortid
Also, should this query be taking 20 seconds to execute?
The tables it's pulling from have 14,000 (Fans) and 20,000 (Photos)
records in them.