May 3, 2012 at 4:28 am
Hi all,
I'm having a bit of trouble trying to order the first query in a union join. I would like to select 2 records from a larger selection to display at the top of my results & then the remaining results show underneath.
Here is a cut down version of what i'm trying to do:
CREATE TABLE Entries
(
entriesID INT IDENTITY(1,1) NOT NULL
,name VARCHAR(20) NOT NULL
,packageLevel TINYINT NOT NULL
)
INSERT INTO Entries (name,packageLevel) VALUES ('aa',0)
INSERT INTO Entries (name,packageLevel) VALUES ('bb',1)
INSERT INTO Entries (name,packageLevel) VALUES ('cc',1)
INSERT INTO Entries (name,packageLevel) VALUES ('dd',1)
INSERT INTO Entries (name,packageLevel) VALUES ('ee',1)
INSERT INTO Entries (name,packageLevel) VALUES ('ff',1)
INSERT INTO Entries (name,packageLevel) VALUES ('gg',1)
INSERT INTO Entries (name,packageLevel) VALUES ('hh',1)
INSERT INTO Entries (name,packageLevel) VALUES ('ii',0)
INSERT INTO Entries (name,packageLevel) VALUES ('jj',0)
INSERT INTO Entries (name,packageLevel) VALUES ('kk',0)
INSERT INTO Entries (name,packageLevel) VALUES ('ll',0)
INSERT INTO Entries (name,packageLevel) VALUES ('mm',0)
INSERT INTO Entries (name,packageLevel) VALUES ('nn',0)
INSERT INTO Entries (name,packageLevel) VALUES ('oo',0)
INSERT INTO Entries (name,packageLevel) VALUES ('pp',0)
INSERT INTO Entries (name,packageLevel) VALUES ('qq',0)
INSERT INTO Entries (name,packageLevel) VALUES ('rr',0)
INSERT INTO Entries (name,packageLevel) VALUES ('ss',0)
CREATE PROCEDURE usp_getEntry
AS
SELECT TOP 2
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM Entries
WHERE packageLevel = 1
UNION
SELECT
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM Entries
WHERE packageLevel <> 1
ORDER BY packageLevel desc, RANDOM
EXEC usp_getEntry
As you can see in the data the records bb,cc,dd,ee,ff,gg,hh all have a package level of 1. I need to randomly select 2 of these to show at the top of the results. But as the "Select top 2" happens before the "order by packageLevel desc, RANDOM" at the end of the query. It only ever randomises the top 2 records. bb & cc. If I could order an "Order by" to the first query that would be exactly what I want, unfortunately SQL does not alow me to do this. Does anyone know of a better way to randomly select 2 (package level 1) records to display at the top of my results.
I hope this is clear.
Darren
May 3, 2012 at 12:20 pm
Hi Can you check if this helps....
SELECT * FROM (SELECT
Top 2
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM Entries
WHERE packageLevel = 1
ORDER BY packageLevel desc, RANDOM) TableA
UNION
SELECT * FROM (SELECT top 12--should be whatever u want in the second table
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM Entries
WHERE packageLevel <> 1 ORDER BY packageLevel desc, RANDOM) TableB
ORDER BY packageLevel desc, RANDOM
May 3, 2012 at 1:01 pm
Similar to the above:
CREATE TABLE dbo.Entries
(
entriesID INT IDENTITY(1,1) NOT NULL
,name VARCHAR(20) NOT NULL
,packageLevel TINYINT NOT NULL
);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('aa',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('bb',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('cc',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('dd',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('ee',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('ff',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('gg',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('hh',1);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('ii',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('jj',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('kk',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('ll',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('mm',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('nn',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('oo',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('pp',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('qq',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('rr',0);
INSERT INTO dbo.Entries (name,packageLevel) VALUES ('ss',0);
WITH Top2Entries AS (
SELECT TOP 2
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM
dbo.Entries
WHERE
packageLevel = 1
ORDER BY
[RANDOM]
)
SELECT
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM
Top2Entries
UNION
SELECT
entriesID
,name
,packageLevel
,NEWID() AS [RANDOM]
FROM
dbo.Entries
WHERE
packageLevel <> 1
ORDER BY
packageLevel desc,
RANDOM
DROP TABLE dbo.Entries;
May 4, 2012 at 3:31 am
Thank you somabk2007 & Lynn. Both solutions work perfect. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply