Simple SQL problem

[SteveCulshaw]

Can anyone help on this SQL, as I'm just going round in circles?
I've got a table CNCODE with fields CODEVALUE and LISTNAME, and I need to get just the first codevalue for each of the different listvalues

So the table has
CODEVA LISTNAME
------ --------------------
COM 16BH
OFF 16BH
RES 16BH
COM 16H
OFF 16H
RES 16H
SCHEDB AACODE
AGENT AATYPE
ALIAS AATYPE
DIST AATYPE
...

What I need is
CODEVA LISTNAME
------ --------------------
COM 16BH
COM 16H
SCHEDB AACODE
AGENT AATYPE
...

 

[jimbopalmer]

select min(codeva), listname from cncode group by listname; I tried to remain child-like, all I acheived was childish.

 

[SteveCulshaw]

Excellent, many thanks - that's a really neat way to do what I want.
Of course, and there's always one is there
Is there any way to say bring back the top 3 or 4 values for each LISTNAME ??

 

[angiole]

What is it you are really trying to accomplish here?

 

[SteveCulshaw]

I suppose the final "goal" is creating documentation, and including in it some examples of the code lists, but not every single code. I originally thought one example of each, but now wonder if it's possible to create a SQL statement that will select a set number, say 3 or 4, so that the examples in the notes are a bit clearer.

 

[karluk]

Aha, I think this can be done with analytic functions. These functions are relatively new and I don't have too much experience with them, but try

select listname, codevalue from
(select listname, codevalue,
row_number() over (partition by listname order by codevalue) as seq_no
from cncode)
where seq_no <= 3

The row_number() function assigns a sequence number which is reset every time listname changes. That gives you a selection criteria to pick the top three codevalues from each listname.

 

[Guest_imported]

SELECT *
FROM CNCODE CN1
WHERE CN1.CODEVALUE =
(SELECT MIN(CN2.CODEVALUE)
FROM CNCODE CN2 WHERE CN1.LISTNAME = CN2.LISTNAME);