07. december 2010 - 21:44Der er
5 kommentarer og 1 løsning
TOP X fra sub select med union?
Har følgende sql som ikke virker:
select top 5 from (select TimeOfVisit as Date, Host as Host, Guest,1 from ProfileVisit union all select Replied as Date,ProfileIdTo as Host,ProfileIdFrom as Guest,2 from mail union all select DateProfileAdded as Date,ProfileIdAdded as Host, ProfileIdAdder as Guest,3 from ProfileFavorite) order by date
Det jeg ønsker er at kunne trække et odered top x resultat ud fra en select der inkludere union.
select top 5 * from (select TimeOfVisit as Date, Host as Host, Guest,1 from ProfileVisit union all select Replied as Date,ProfileIdTo as Host,ProfileIdFrom as Guest,2 from mail union all select DateProfileAdded as Date,ProfileIdAdded as Host, ProfileIdAdder as Guest,3 from ProfileFavorite) as p order by date
Ja havde faktisk ikke lige set den manglede på 1. du havde været så grundig at sætte aliaser alle steder.
I virkeligheden kan man undgå det i en union, det er kun første select som behøver det, eks.vis:
select top 5 * from (select TimeOfVisit as Date, Host as Host, Guest, 1 as Number from ProfileVisit union all select Replied, ProfileIdTo, ProfileIdFrom, 2 from mail union all select DateProfileAdded, ProfileIdAdded, ProfileIdAdder, 3 from ProfileFavorite) as p order by date
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.