May 13, 2011 at 4:27 am
Hi,
I have a SQL query, like so:
SELECT DISTINCT ID, Name FROM Table
This brings up all the distinct IDs (1...13), but in the 13 IDs, it repeats the name (as it comes up twice). The order of the query (ID, Name) has to be kept the same as the app using this query is coded with this assumption.
The resultset looks like:
1 A
2 B
3 C
......
13 A
Is there a way to get just the rows where the name (A, C, etc) does not repeat in the resultset? I need to keep the ID and I don't want to hardcode assumptions into the query.
Thanks
May 13, 2011 at 4:37 am
Check for null value . If column contains null value then might be distinct will not work
May 13, 2011 at 4:39 am
There are no null values.
May 13, 2011 at 4:46 am
You'll need to select the distinct values and join the result set back to the original table in order to get the value for name. You'll need to decide how you resolve which name you'll choose where there are more than one for any particular value of ID.
John
May 13, 2011 at 4:56 am
info-849042 (5/13/2011)
Hi,I have a SQL query, like so:
SELECT DISTINCT ID, Name FROM Table
This brings up all the distinct IDs (1...13), but in the 13 IDs, it repeats the name (as it comes up twice). The order of the query (ID, Name) has to be kept the same as the app using this query is coded with this assumption.
The resultset looks like:
1 A
2 B
3 C
......
13 A
Is there a way to get just the rows where the name (A, C, etc) does not repeat in the resultset? I need to keep the ID and I don't want to hardcode assumptions into the query.
Thanks
You will have to decide which row (ID) to keep for each name which has duplicates - typically the minimum or the maximum value. This example keeps the minimum:
CREATE TABLE #Table (ID INT, Name CHAR(1))
INSERT INTO #Table (ID, Name) VALUES (1, 'A')
INSERT INTO #Table (ID, Name) VALUES (2, 'B')
INSERT INTO #Table (ID, Name) VALUES (3, 'C')
INSERT INTO #Table (ID, Name) VALUES (4, 'A')
SELECT
ID = MIN(ID),
Name
FROM #Table
GROUP BY Name
ORDER BY MIN(ID)
You could also use a CTE and ROW_NUMBER() OVER (PARTITION BY name ORDER BY ID).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 13, 2011 at 9:44 am
Some options: -
DECLARE @TABLE AS TABLE (ID INT IDENTITY, Name CHAR(1))
INSERT INTO @TABLE (Name)
SELECT TOP 250000
CHAR(ROUND(RAND(CHECKSUM(NEWID())) * 180,2))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
--Keep smallest ID
SELECT MIN(ID) AS ID, Name
FROM @TABLE
GROUP BY Name
ORDER BY MIN(ID)
;WITH CTE AS (
SELECT ID, Name,
ROW_NUMBER() Over (partition by Name ORDER BY ID ASC) AS rn
FROM @TABLE)
SELECT ID, Name
FROM CTE
WHERE rn = 1
ORDER BY ID
--Keep largest ID
SELECT MAX(ID) AS ID, Name
FROM @TABLE
GROUP BY Name
ORDER BY MAX(ID)
;WITH CTE AS (
SELECT ID, Name,
ROW_NUMBER() Over (partition by Name ORDER BY ID DESC) AS rn
FROM @TABLE)
SELECT ID, Name
FROM CTE
WHERE rn = 1
ORDER BY ID
--EDIT-- Blergh. Should've read the thread. See "ChrisM@home" reply.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply