Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

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

why are these records not your expected results?

C1 C2 C3

L01 1 123

L01 2 345

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 ?

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 ?! ;-)

```
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
```

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

:) yes, that bit was what I could not understand.

Not sure I would use

> 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
```

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
```

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.

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

Sorry, I found the way

I didn't realize that "derived" was the alias of that "virtual table"

Thank !

I didn't realize that "derived" was the alias of that "virtual table"

Thank !

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

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.IU

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

```
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
```

the left join & coalesce functions probably not really needed due to the where clause
```
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
```

http://sqlfiddle.com/#!3/2dffc/22The 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.

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?

All Courses

From novice to tech pro — start learning today.

C1 C2 C3

L01 1 123

L01 2 345

not sure how K09,1,123 overridex L01,1,123see http://sqlfiddle.com/#!3/2dffc/1Open in new window