CODEWITHSUNDEEP
sqlsql-servercase
Jeff
Jeff

Reputation: 449

Selecting a row based on column value

I have a select statement in SQL. The select statement is selecting a licenseNo and a LicenseID. Basically, I want it to return the LicenseNo depending on which LicenseTypeID it is.

For example, I want it to return the LicenseNo if the LicenseTypeID = 6 first, then if there is no ID that equals 6, return the LicenseNo where the LicenseTypeID = 5 and so on.

Right now, I have a join that is causing multiple LicenseNos to be returned because there are multiple LicenseTypeIDs. I only want it to return the LicenseNo and row in which the ID of 6 takes precedence, then 5, then 4 and so on. It looks something like this right now:

Select a.Name,
       a.addressNo,
       b.LicenseNo,
       LicenseTypeID
  from addressbook a 
  join licenses b
    on a.addressNo = b.addressNo

Returns
111    CompanyA  1234   6
111    CompanyA  2222   4

So I only want it to return the first row, and if that ID doesnt exist (6) I want it to return the second row of 4.

Upvotes: 0

Views: 68

Answers (2)

Harshil
Harshil

Reputation: 411

Try this.

SELECT * FROM 
(SELECT ROW_NUMBER() OVER 
      (PARTITION BY l.licenseno ORDER BY l.licenseno DESC) NO,
       a.Name,
       a.addressNo,
       b.LicenseNo,
       LicenseTypeID
  from addressbook a 
  join licenses b
    on a.addressNo = b.addressNo) AS t WHERE no = 1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

You need a subselect to determine the maximum licence number for each address:

select 
  a.name,
  a.addressno,
  l.licenseno,
  l.licensetypeid
from addressbook a 
join licenses l on l.addressno = a.addressno
where l.licenseno =
(
  select max(licenseno)
  from licenses
  where licenses.addressno = a.addressno
);

Upvotes: 1

Related Questions