Retrieve only first rows in a grouping 3

[pwills]

I have the following table sorted by town.

London John Y
London Pete N
London Fred N
Paris Gina Y
Geneva Suzy N

How do I get the first person in each town? I want to produce:

London John Y
Paris Gina Y
Geneva Suzy N

It's easy enough to just produce the first column, but I cannot seem to get the 2nd and 3rd columns.

 

[r937]

how do you recognize the "first" in a group? since there is no order in a relational database, it has to be based on some other column -- dateadded? lowest surname alphabetically?

rudy

http://rudy.ca/

 

[pwills]

Good point! They are ordered by the 3rd colum EmployeeFlag in descending order i.e. Y then N

 

[r937]

you realize, of course, that if you sort rows by EmployeeFlag within Town, you will get different "first" people depending on whether there's an R in the month name

two more questions before i give the sql a try

what is the primary key of the table?

what database are you actually using? i realize you posted this in the ansi sql forum, and that's fair, but i'm wondering if you might be interested in a solution which just works, so if you can use TOP or LIMIT that would be cool

rudy

 

[PruSQLer]

Rudy,

Where the heck did you get month name out of his post?

 

[r937]

PruSQLer: exactly my point!!

but i suppose i should have made it in a more straightforward, less subtle manner

sorry, pwills

select Town, Person, EmployeeFlag 
  from thetable
order 
    by Town asc
     , EmployeeFlag desc

could return different "first" people in each group because there appear to be multiple people with the same EmployeeFlag

of course, if pwills wants just "a" row from each group, rather than "the first" row from each group, that'd be different

rudy

 

[pwills]

I am using sql server. To be more precise, I want one user per town. If he is an employee (Y) then I want that one, otherwise I want any employee. So on a table:

London Pete N
London Fred N
Paris Gina Y
Geneva Suzy N

It would just pick either Pete or Fred. Seems such a simple request but it has completely stumped me.

 

[pwills]

The primary key is Person

 

[r937]

sql/server is good, because you can use TOP

that's not ansi sql, but it does what you want

select Town, Person, EmployeeFlag 
  from thetable xx
 where Person 
     = ( select TOP 1 Person
           from thetable
          where Town = xx.town
       order by EmployeeFlag descending )

rudy

 

[pwills]

I was very excited because this seems to be the code I need. Trouble is the query just doesn't finish. I stopped execution after 40 seconds and it had got to record 600 out of a total 1900 records. Here is the exact code:

select company, user_id, maincontact
from pt5_user xx
where user_id
= ( select TOP 1 user_id
from pt5_user
where company = xx.company
order by maincontact desc )

I need one contact at each company and it must be the maincontact if one exists.

Can't believe the query is so complicated. All I need is an imaginary "FIRST" aggregate function but it just doesn't exist.

 

[barbarul2001]

try this

select top 1 company, user_id, maincontact
from pt5_user xx
where maincontact=(select max(maincontact)
from pt5_user where company = xx.company )

or this (it works in sybase)

select top 1 company, user_id, maincontact
from pt5_user xx
where maincontact=max(maincontact)


or this

select top 1 company, user_id, maincontact
from pt5_user xx
where 0=(select count(*) from pt5_user where company = xx.company and xx.maincontact<maincontact )

good luck !
web/sql developer

 

[barbarul2001]

Rectification for sybase:

select top 1 company, user_id, maincontact
from pt5_user xx
having maincontact=max(maincontact)
web/sql developer

 

[angiole]

Good Morning All,

The man (woman ?) just wants to return a name.

I've used the following to answer a quickie question.

PWills needs to return a contact name, with preference to those flagged with 'Y'.

So then we can select the city, concatenating the flag to the name to select the maximum name (PWills doesn't care as I understand).

Select   City,
         max(Flag || " " || Name) as Contact_Name
From     Table1
Group by City

I know, I know, you don't want to see the flag printed against the name, then simply substr your select, as...

Select   City,
         substr(max(Flag || " " || Name),2,30) as Contact_Name
From     Table1
Group by City

The above is Oracle sql, whcih uses the || to concatenate, but I think SQL Server uses a +.

You might also need to use the Right or mid function instead of substr, but I hope you get the idea.

Cheers.

AA 8~)

 

[pwills]

angiole/barbarul,

Firstly: how absolutley fantastic that one get get such thoughtful advice, and so fast, and for free. Here in England, I have raised the issue at dinner parties only to be met with accusations of nerdery. Long live the Internet.

Secondly:

I had just managed to exactly the conclusion of angiole, but it took me 3 days of deep thought and irritation to my wife and children. It is rather elegant, but my guess it is fairly CPU intensive. Still it works!

I shall try:

select top 1 company, user_id, maincontact
from pt5_user xx
having maincontact=max(maincontact)

on SQL Server and report back what happens. It does strike me as most odd that SQL doesn't &quot;naturally&quot; permit such a query...

 

[angiole]

No problem, but are you are sure the code you wrote works?
Without a group by on city, I think it will only return one row.


AA 8~)

 

[pwills]

Barbaru's 1st suggestion only returns 1 row. I get an error on the 2nd:
Column 'xx.company' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
I didn't try the 3rd.

Angiole's concatenation method does indeed work, but I am hopeful of finding a more native query for such a simple problem.

 

[r937]

try a variation

select Town, Person, EmployeeFlag 
  from thetable xx
 where Person
     = ( select max(Person)
           from thetable 
          where Town = xx.town 
            and EmployeeFlag 
              = ( select max(EmployeeFlag)
                    from thetable
                   where Town = xx.town ) )

if there are only 1900 rows and it's sql/server you're running, i'd go talk to your dba about indexes

rudy

 

[pwills]

Whoah! It works! I had to make a slight adjustment to handle nulls in the flag:

select company, user_id, maincontact
from pt5_user xx
where user_id
= ( select max(user_id)
from pt5_user
where company = xx.company
and isnull(maincontact,'a')
= ( select max(isnull(maincontact,'a'))
from pt5_user
where company = xx.company ) )
order by company

Once I get back to my enterprise manager, I can easily compare the cost of this query with the concatenation one and will report back on the most efficient, bearing in mind that only user_id is indexed. What I slightly prefer about this latest method is that it appears to be more pure sql and isn't dependent on the data structure of the table.

A really big thank you to all of you for helping. If any of you know how, perhaps these two solutions ought to be in the FAQ?