[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Difficult SQL query in SQL server 2005

Posted on 2013-05-27
21
Medium Priority
?
399 Views
Last Modified: 2013-05-28
I need to extract data from a SQL server 2005 table
The table layout looks like this
C1 char(3)
C2 integer
C3 integer
.../...
The primary key is C1,C2
Each line in the table describe an object (a real !)
The same objet may appears several time
As the objet identifier is C3, this means there may be several couple (C1,C2) for the same C3
I need to extract from this table the list of objects for which the value of C1 is given but only those lines from the tables that correspond to the highest C1,C2 couples for which of course C1 is not the given value
I give an example
Table contains this C1,C2,C3)
(G12,1,345)
(G12,4,123)
(K09,1,123)
(L01,1,123)
(L01,2,345)
(L01,3,678)
The query result should be
(G12,1,345)
(K09,1,123)
C3=678 does not return anything because there is no 'previous' occurrence of it
C3 = 123 returns the last occurrence K09,1,123 (and not G12,4,123 which is "older")
C2 = 345 returns G12,1,345
0
Comment
Question by:LeTay
  • 10
  • 9
  • 2
21 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39199086
this doesn't meet you expected results, but it does produce this:
C1      C2      C3
L01      1      123
L01      2      345
not sure how K09,1,123 overridex L01,1,123 see http://sqlfiddle.com/#!3/2dffc/1 
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3 order by c1 desc, c2 desc) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 1 and c_ref > 1

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39199104
perhaps I should make the question clear...
why are these records not your expected results?
C1      C2      C3
L01      1      123
L01      2      345
0
 

Author Comment

by:LeTay
ID: 39199288
Maybe I forgot to mention that for my example value L01 is the given value of C1
So I need to find values (x,y,z) where x is not L01, z is somewhere specified in another row of the table and the x is the highest existing
In the example, the last row where z = 123 is found, not being the L01,.. row is K09,1,123
Etc...
Hello PortletPaul, does the query you propose give the expected result ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LeTay
ID: 39199305
Hello PortletPaul,
I added the condition in the inside select (where C1 < 'L01') and ... it works !
I know SQL since ...35 years but your wonderful syntax I do not understand, despite the fact it works fine !
Can you please explain, so I will not die as an idiot ?!   ;-)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39199515
Modified Paul's sql to meet your requirement.
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3 order by c1 desc, c2 desc) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 2
order by c1,c2,c3

Open in new window


http://sqlfiddle.com/#!3/2dffc/7
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39199517
I removed the count(*) which I believe is not required.

http://sqlfiddle.com/#!3/2dffc/8
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39199929
>>I forgot to mention that for my example value L01 is the given value of C1
:) yes, that bit was what I could not understand.

Not sure I would use where C1 < 'L01'  here because it will prohibit the complete calculation of occurrences and I'm not sure we should assume that
> L01 does occur for each C3 value
> L01 occurs only once for each C3 value

So I would suggest the following:
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3
                             order by
                              case when c1 = 'L01' then 'a' else c1 end DESC
                            , c2 DESC) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 1 and c_ref > 1

Open in new window

see http://sqlfiddle.com/#!3/2dffc/15 
C1    C2    C3    ROW_REF  C_REF
K09    1    123    1       3
G12    4    123    2       3
L01    1    123    3       3

G12    1    345    1       2
L01    2    345    2       2

L01    3    678    1       1

Open in new window

Regarding explanation please see the above results of the inner subquery and then you will need to understand the nice advantages of "windowing functions". I have used 2 of these which are:

row_number() over (partition by ... order by .... )
count() over (partition by ...)

for more refer to "the over clause"

"partition by" is a little similar to "group by", basically this bit says do some calculations "within a cluster of records that share value(s)"

"order by" is I hope obvious

hence:
"row_number() over (partition by c3 ..."
will produce a unique integer value "within the cluster of records that share a c3 value" and then that unique number is provided according to the "order by"
e.g. K09,1,123 gets a row_number() of 1 and so on as shown in the table above.

nb: row_number() is considered a "ranking function" so it supports "order by"
below, count() is an "aggregate function" which does not support "order by"

For the count(*) over (partition by c3) this simply counts the occurrences of each c3 but supplies that value to each row "within the cluster" so you see 3 repeated 3 times and 2 repeated twice in the c_ref column in table above.

@Sharath, if L01 does not occur, or if L01 should occur more than once, for a C3 value then row_ref = 2 could be wrong I think.
0
 

Author Comment

by:LeTay
ID: 39200604
Hello PorletPaul,
Thanks for all these very interesting information
Now in fact I need a little bit more
I rewrote your select like this, adding the condition on C1 (see below)
What I need more is to join that select to the same Table in order, precisely, to get the C1 and C2 where C1 is the specific value
So (based on my first example), I need this
(G12,1,345,L01,2)
(K09,1,123,L01,1)
I have to write a join but have difficulty as the first select is a kind of "virtual" table
Can you help ?
Thanks

select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3
                             order by
                              case when c1 = 'L01' then 'a' else c1 end DESC
                            , c2 DESC) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
        where C1 < 'SPECIFICVALUE'
      ) as derived
where row_ref = 1 and c_ref > 1
0
 

Author Comment

by:LeTay
ID: 39200704
Sorry, I found the way
I didn't realize that "derived" was the alias of that "virtual table"
Thank !
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39200705
I believe you will need to treat the base table as 2 'virtual tables', by this I mean:

a. a selection of only 'L01' records
b. a selection excluding 'L01' records

then join a to b, but I think this only viable join is through C3

I think... I'll see what I can do

but: Do you want to filter by C1 or C3? seems more logical to me to search for 123 or 345
0
 

Author Comment

by:LeTay
ID: 39200717
Hello PortletPaul
Here is the solution I found
I wrote the select with the real tables and columns and it works
Thanks !
select A.Code_Vente,A.Lot_No,A.IUL,B.Code_Vente,B.Lot_No
from (select Code_Vente,Lot_No,IUL,
             row_number() over (partition by IUL order by Code_Vente desc, Lot_No Desc) as Row_Ref,
             count(*) over (partition by IUL) as c_ref
      from  Lots
      where Code_Vente < 'L09') as A,
      Lots B
where A.IUL        = B.IUL
and   B.Code_Vente = 'L09'
and   A.row_ref    = 1 and A.c_ref > 1
order by 1,2,3
0
 

Author Closing Comment

by:LeTay
ID: 39200720
Magic SQL queries !
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39200748
try this please:
select
  coalesce(NL.C1, L.C1) C1
, coalesce(NL.C2, L.C2) C2
, coalesce(NL.C3, L.C3) C3
, L.C1 LC1
, L.C2 LC2
, L.C3 LC3
from (
      select * from yourTable where C1 = 'L01' 
     ) as L
left join (
            select
                c1
              , c2
              , c3
              , row_number() over (partition by c3
                                     order by c1 desc, c2 desc) as row_ref
            from yourTable where C1 <> 'L01' 
          ) as NL
                 on L.C3 = NL.C3
where NL.row_ref = 1

Open in new window

the left join & coalesce functions probably not really needed due to the where clause
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39200754
this one I think
select
  NL.C1
, NL.C2
, NL.C3
, L.C1 LC1
, L.C2 LC2
, L.C3 LC3
from (
      select * from yourTable where C1 = 'L01' 
     ) as L
inner join (
            select
                c1
              , c2
              , c3
              , row_number() over (partition by c3
                                     order by c1 desc, c2 desc) as row_ref
            from yourTable where C1 <> 'L01' 
          ) as NL
                 on L.C3 = NL.C3
where NL.row_ref = 1

Open in new window

http://sqlfiddle.com/#!3/2dffc/22

The previous left join/coalesce query might be useful (not sure) you can see what it does without the where clause at the sqlfiddle url above.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39200760
very good! well done!

just as a pointer, it's my view that overall its easier if we deal with the real tables/fields. It means you don't have to "translate" - and it really makes little difference at this end.

Cheers, Paul

"code_vente" ces't que le francias?
0
 

Author Comment

by:LeTay
ID: 39201045
Hello PortletPaul,
The select is related to public sale auctions (my customer)
C1 is the sale, C2 the object number (order of the sale) and C3 the object identifier
This select allows us to get the list of objects that were already presented in a previous sale and in fact to get the last one
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39201101
interesting, did the last query work for you?
0
 

Author Comment

by:LeTay
ID: 39201254
which one precisely ?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39201278
ID: 39200754 (the most recent)
0
 

Author Comment

by:LeTay
ID: 39201287
Sorry, I didn't try
I used mine which works
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39201310
no problem. Cheers, Paul
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question