INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return unique row from a given set of rows

Return unique row from a given set of rows

(OP)
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

RE: Return unique row from a given set of rows

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

RE: Return unique row from a given set of rows

Highlight text you want to align and use Pre tag:

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
 
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.

RE: Return unique row from a given set of rows

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close