Distinct on third column

[TekLight]

Hi,
I have a table like this ,how to get all three columns in a SELECT query with distinct NEWORDER values :

ID OLDORDER NEWORDER
4853814 NULL 1Z6W688A0196725307
4878543 NULL 1Z6W688A0497292312
4878543 798377318718 798377318718
4878543 798377318718 798377318718

any help please ?

 

[djj55]

You could do something like

SELECT MAX(ID) AS MXID, NEWORDER FROM MyTable GROUP BY NewOrder

Then add in the OldOrder value.

However with the data shown a simple distinct would work

SELECT DISTINCT * FROM MYTABLE

Though I may not understand the problem properly.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[imex]

Hi,

Try something like that:

with CTE as
(
    select t.*, ROW_NUMBER() OVER(PARTITION BY NEWORDER ORDER BY ID) as RowNum
    from @MyTable as t
)

select ID, OLDORDER, NEWORDER 
from CTE
where RowNum = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]