Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vmasanas
Starting Member
3 Posts |
 Posted - 2008-11-26 : 01:38:10
|
| I've been struggling with this for sometime but I can't figure the answer, maybe someone can help.Lets say I have a table PERSON with a couple fields:- personid (identity)- personname- ....and another table VEHICLES where for each person I have a list of the vehicles it owns:- vehicleid (identity)- personid- type- ...I need a query that return for each PERSON a random record of each VEHICLE type. And I need both the person info and the vehicle info on the query.So far I've got this, which returns the last entered VEHICLE of a given type:select P.*,V.*from PERSON Pjoin ( select id=max(vehicleid),personid from VEHICLE group by personid, type) x on p.personid = x.idjoin VEHICLE V on x.id = v.idorder by p.personidusing max and min I can get the first or last, but how would you get something like this that returns a random record?What I need is a record set like this:PersonId=1, ..., VehicleId=18, VehicleType="car", ...PersonId=1, ..., VehicleId=1, VehicleType="bike", ...PersonId=1, ..., VehicleId=123, VehicleType="boat", ...PersonId=2, ..., VehicleId=218, VehicleType="car", ...PersonId=2, ..., VehicleId=11, VehicleType="bike", ......where for each person I get 1 random record from the detail table for each type.I'm sure it should be much easier but I can't see the light.Any ideas? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 01:57:11
|
| [code]SELECT p.PersonId,v.VehicleId, v.VehicleType...FROM(select *,ROW_NUMBER() OVER(PARTITION BY P.personid,V.type ORDER BY NEWID()) AS Seqfrom PERSON Pjoin VEHICLE V on P.personid = v.personid)tWHERE Seq=1 order by Personid[/code] |
 |
|
|
vmasanas
Starting Member
3 Posts |
Posted - 2008-11-26 : 02:20:02
|
| Great, I don't yet understand what it does but seems to be working.Thanks a lot |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-26 : 02:30:46
|
quote:
Originally posted by vmasanas
Great, I don't yet understand what it does but seems to be working.Thanks a lot
NEWID() returns you a random value. you're numbering records based on value generated for each P.personid,V.type group and taking first record which will give you a random row |
|
|
|
vmasanas
Starting Member
3 Posts |
Posted - 2008-11-26 : 04:23:18
|
| I've also tried this one and seems to do the trick. Actually this is a bit different since I'm passing a given type (this will be used in a stored proc), so I get a random record for a given type:select p.*,v.*from person pjoin vehicle v on p.personid=v.personidwhere v.vehicleid=(select top 1 vehicleid from vehicle where personid=p.personid and type=@type order by newid())order by personid |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Selecting random records from details table