Revision letters in a column-Select,Grouby issue 1

[miked123]

A fellow on the CR forum has the following problem:
snip..
Part numbers have a revision letter as follows:
123A-234A
123A-234B
123A-234C I want to show this one only

I posted this SQL statement which works but returns two columns. How can I rejoin the columns?? This puzles me
Thanks MikeD

Select left(p2.pn,8) as pn ,Max(right(p2.pn,1))as Rev
From fd_pn p2
Group by left(p2.pn,8)


Mike Davis
MSsql, VB and Crystal Reports Developer

 

[bperry]

Hi there,
I would try this:
----------------
Select pn
From fd_pn p1
Where p1.pn = (select MAX(pn) from fd_pn p2
where left(p2.pn,8) = left(p1.pn,8))
Group by pn
---------------
rgrds, etc
brian perry

 

[bperry]

Here's another way that sticks pretty close to what you first tried yourself; it just wraps your query inside a 'derived table'.
And, in fact, this approach is much more efficient than my own first suggestion. (Will make a difference if the table is large)
----------------------------
Select pn+Rev as pn from
(
Select left(p2.pn,8) as pn ,Max(right(p2.pn,1))as Rev
From fd_pn p2
Group by left(p2.pn,8)
) as dt
---------------------------
regrds, etc
brian perry

 

[miked123]

Thanks Brian, You made it so simple.
One question please. What does the 'AS dt' do??

Thanks
MikeD Mike Davis
MSsql, VB and Crystal Reports Developer

 

[bperry]

Okay, that's easy, Mike.
-----------------------
Select pn+Rev as pn FROM
(
Select left(p2.pn,8) as pn ,Max(right(p2.pn,1))as Rev
From fd_pn p2
Group by left(p2.pn,8)
) as dt
------------------------
See how we are selecting FROM a thing wrapped up in brackets? That's called a 'derived table', meaning it's not a real table, a sort of a temporary table created in memory, just for the duration of this Select statement.

But SQL needs to know a name to select FROM, and so we have to give this derived table a name that SQL can then refer to. In this example I've given it the name 'dt' (I often do that for these derived tables.) We could have given it any name instead of dt "as MikesTables" would have worked just as well.

there you go!