Solved

Difficult SQL query in SQL server 2005

Posted on 2013-05-27
21
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 2
21 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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