CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-group
May
May

Reputation: 23

Understanding an SQL Query

I'm new to SQL and I've been racking my brain trying to figure out exactly what a query I received at work to modify is stating. I believe it's using an alias but I'm not sure why because it only has one table that it is referring to. I think it's a fairly simply one I just don't get it.

select [CUSTOMERS].Prefix,
[CUSTOMERS].NAME,
[CUSTOMERS].Address,
[CUSTOMERS].[START_DATE],
[CUSTOMERS].[END_DATE]  from [my_Company].[CUSTOMERS]  
where [CUSTOMERS].[START_DATE] =
   (select max(a.[START_DATE])
   from [my_company].[CUSTOMERS] a
where a.Prefix = [CUSTOMERS].Prefix
 and a.Address = [CUSTOMERS].ADDRESS
 and coalesce(a.Name, 'Go-Figure') =
     coalesce([CUSTOMERS].a.Name, 'Go-Figure'))

Upvotes: 1

Views: 131

Answers (3)

shawnt00
shawnt00

Reputation: 17915

@Joshp gave a good answer although I have seen these kinds of queries and worse in all kinds of real applications.

See if the query below gives you the same result though. The queries would not be equivalent in general but I suspect thet are the same with the data you've got. I believe the only assumption I'm making is that the ranges between start and end dates never intersect or overlap which implies that max start and max end are always together in the same row.

select
    c.Prefix, c.NAME, c.Address,
    max(c.START_DATE) as Start_Date,
    max(c.END_DATE) as End_Date
from my_Company.CUSTOMERS as c
group by c.Prefix, c.NAME, c.Address

You'll notice the alias is a nice shorthand that keeps the query readable. Of course when there's only a single table they aren't strictly necessary at all.

Upvotes: 0

joshp
joshp

Reputation: 1892

Here's a shot at it in english...

It looks like the intent is to get a list of customer names, addresses, start dates.

But the table is expected to contain more than one row with the same customer name and address, and the author wants only the row with the most recent start date.

Fine Points:

  • If a customer has the same name and address and prefix as another customer, the one with the most recent start date appears.

  • If a customer is missing the name 'Go Figure' is used. And so two rows with missing names will match, and the one with the most recent start date will be returned. A row with a missing name will not match another row that has a name. Both rows will be returned.

  • Any row that has no start date will be excluded from results.

This does not look like a query from a real business application. Maybe it's just a conceptual prototype. It is full of problems in most real world situations. Matching names and addresses with simple equality just doesn't work well in the real world, unless the names and addresses are already cleaned and de-duplicated by some other process.

Regarding the use of alias: Yes. The sub-query uses a as an alias for the my_Company.CUSTOMERS table.

I believe there is an error on the last line.

[CUSTOMERS].a.Name

is not a valid reference. It was probably meant to be

[CUSTOMERS].Name

Upvotes: 5

max
max

Reputation: 2817

I assume, it selects records about customers records from table [CUSTOMERS] whith the most recent [CUSTOMERS].[START_DATE]

Upvotes: 1

Related Questions