Return unique row from a given set of rows 2

[yorge]

Hi Guys,

Given the sample data on a table:

CODE CODESET LANGUAGE NAME ORDER
----------------------------------------------------------------------------------------------
PA_KNOWN FLAG_KNOWN ENG Known 10
PA_KNOWN FLAG_KNOWN NOB Kjent 10
PA_UNKNOWN FLAG_KNOWN ENG Unknown 20
PA_UNKNOWN FLAG_KNOWN NOB Ukjent 20
PA_YES FLAG_YES_NO ENG Yes 30
PA_YES FLAG_YES_NO NOB Ja 30
PA_NO FLAG_YES_NO ENG No 40
PA_NO FLAG_YES_NO NOB Nei 40
PA_DAILY FLAG_FREQ ENG Daily 50
PA_DAILY FLAG_FREQ NOB Av og til 50
----------------------------------------------------------------------------------------------

I wanted a query that will output unique CODESET whose LANGUAGE = 'ENG' and having the smallest value for ORDER

So output will be like:

CODE CODESET LANGUAGE NAME ORDER
----------------------------------------------------------------------------------------------
PA_KNOWN FLAG_KNOWN ENG Known 10
PA_YES FLAG_YES_NO ENG Yes 30
PA_DAILY FLAG_FREQ ENG Daily 50
----------------------------------------------------------------------------------------------


Thanks in advance,
Yorge

 

[yorge]

oops...sorry, I can't seem to align property the columns...but it should be CODE, CODESET, LANGUAGE, NAME and ORDER (5 columns)

 

[Andrzejek]

Highlight text you want to align and use Pre tag:

[pre]
CODE CODESET LANGUAGE NAME ORDER
PA_KNOWN FLAG_KNOWN ENG Known 10
PA_KNOWN FLAG_KNOWN NOB Kjent 10
PA_UNKNOWN FLAG_KNOWN ENG Unknown 20
PA_UNKNOWN FLAG_KNOWN NOB Ukjent 20
PA_YES FLAG_YES_NO ENG Yes 30
PA_YES FLAG_YES_NO NOB Ja 30
PA_NO FLAG_YES_NO ENG No 40
PA_NO FLAG_YES_NO NOB Nei 40
PA_DAILY FLAG_FREQ ENG Daily 50
PA_DAILY FLAG_FREQ NOB Av og til 50
[/pre]
"I wanted a query that will output unique CODESET whose LANGUAGE = 'ENG'
and having the smallest value for ORDER

So output will be like:"
[pre]
CODE CODESET LANGUAGE NAME ORDER
PA_KNOWN FLAG_KNOWN ENG Known 10
PA_YES FLAG_YES_NO ENG Yes 30
PA_DAILY FLAG_FREQ ENG Daily 50
[/pre]
How about:
Select CODE, CODESET, LANGUAGE, NAME, MIN(ORDER) as MyOrder
From MyTable
Group By CODE, CODESET, LANGUAGE, NAME
Having LANGUAGE = 'ENG'

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[cmmrfrds]

Select * From (
Select CODE, CODESET, LANGUAGE, NAME, ORDER
RANK() OVER (PARTITION BY LANGUAGE ORDER BY ORDER asc) RNK
From MyTable
Where LANGUAGE = 'ENG'
)
ORDER BY
RNK