2 Select Distinct
I have a query
That has this table
resulting to this:
But i want it to result in the bottom result of the value column instead:
SELECT key, value
FROM tableX
( SELECT key, value,
ROW_NUMBER() OVER (PARTITION BY key
ORDER BY whatever) --- ORDER BY NULL
AS rn --- for example
FROM tableX
) tmp
WHERE rn = 1 ;That has this table
key value
=== =====
one test
one another
one value
two goes
two here
two also
three exampleresulting to this:
key value
=== =====
one test
two goes
three exampleBut i want it to result in the bottom result of the value column instead:
key value
=== =====
one value
two also
three example
You indicate that the "natural order" of the rows will produce the wanted result. But tables HAVE NO ORDER so there is no guarantee the result will match you expectation UNLESS there is something else we can definitely use to order by, perhaps an auto-increment ID?
SELECT key, value
FROM tableX
( SELECT key, value,
ROW_NUMBER() OVER (PARTITION BY key
ORDER BY ID DESC) --- ORDER BY reverse sequence of insert
AS rn --- although this isn't perfect
FROM tableX
) tmp
WHERE rn = 1 ;
NB:
using TOP requires an ORDER BY or the results are unpredictable
using TOP requires an ORDER BY or the results are unpredictable
Hi Author,
What is the logic by which you will pick the Value column.? Check out the below Query. I just need the Order by logic.
Try below to see if it works for you-
;WITH CTE AS
(
SELECT key, value
FROM tableX
( SELECT key, value,
ROW_NUMBER() OVER (PARTITION BY key
ORDER BY whatever) --- ORDER BY NULL
AS rn --- for example
FROM tableX
) tmp
WHERE rn = 1
)
SELECT c.Key, p.Value FROM (SELECT DISTINCT Key FROM CTE) c
CROSS APPLY
(
SELECT TOP 1 b.value FROM
CTE b WHERE b.key = c.key
ORDER BY b.Value DESC
)p
What is the logic by which you will pick the Value column.? Check out the below Query. I just need the Order by logic.
Try below to see if it works for you-
;WITH CTE AS
(
SELECT key, value
FROM tableX
( SELECT key, value,
ROW_NUMBER() OVER (PARTITION BY key
ORDER BY whatever) --- ORDER BY NULL
AS rn --- for example
FROM tableX
) tmp
WHERE rn = 1
)
SELECT c.Key, p.Value FROM (SELECT DISTINCT Key FROM CTE) c
CROSS APPLY
(
SELECT TOP 1 b.value FROM
CTE b WHERE b.key = c.key
ORDER BY b.Value DESC
)p
ASKER
something isn't jiving with the syntax
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
never mind, it works pretty good.
Excellent !! Glad to help !
Thank You!
Pawan
Thank You!
Pawan
pls try this..
Open in new window
Hope it helps!