Select 1st record

[razchip]

I need to run a query where I pull out the first record only for each of the orders in tblOrder

TblOrders tblPcak

Pick SalesID (Des) Doc SalesID (Asc)
C 235b 1 123a
B 235b 2 123a
A 235b 3 123a
E 123a 4 123a
D 123a 5 123a
C 123a 1 235b
B 123a 2 235b
A 123a 3 235b


I would like the output to give me two records;
1st record = C 235b & 1 235b
2nd record = E 123a & 1 235b


Thanks for the help.
Greg

 

[SkipVought]

Hi,

What does "first" mean?
Pick=MAX(Pick), MIN(Doc)?

Select
  Max(Pick)
, O.[SalesID]
, Min(Doc)
From 
  TblOrders O
, tblPcak   P
Where O.SalesID = P.SalesID 
Group By
  O.[SalesID]

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[razchip]

Sorry for the confusion, SalesID is the related record for each table. I just need the first matching record from each table with like SalesID #'s.

From the 1st table (tblOrders), I want the 1st descending salesID and Pick# (235b C), from the 2nd table (tblPcak), I want the 1st ascending SalesID and Doc# (235b 1).

The second record would be (123a E) and (123a 1). The remaining six records would not appear in the results.

Thanks for the help.
Greg

 

[SkipVought]

There is no sort order in a table.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[razchip]

I have an index set in the tabel that sorts the data in the manner shown.

Thanks for the help.
Greg

 

[SkipVought]

The relational database model was conceived by E. F. Codd in 1969, then a researcher at IBM. The model is based on branches of mathematics called set theory and predicate logic. The basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables. This model was in vast contrast to the more traditional database theories of the time that were much more complicated, less flexible and dependent on the physical storage methods of the data.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

However you THINK that your table is ordered, it's NOT.

You must use CRITERIA to select the rows you want.


Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[lespaul]

an index is just a data structure that allows for faster operations when you are searching a table. it doesn't "sort" the data as shown. There is no ORDERING of a table...you can't say I want the FIRST record...that doesn't mean anything. You have to determine the CRITERIA that determines what you see as the "first" record.

So you have these two tables which have the SalesID as the common field. For each SalesID you want to find:

1. From tblOrders: the highest/lowest Pick?

2. From tblPack: the highest/lowest Doc?

[tt]
tblOrders tblPack
Pick SalesID (Des) Doc SalesID (Asc)
C 235b 1 123a
B 235b 2 123a
A 235b 3 123a
E 123a 4 123a
D 123a 5 123a
C 123a 1 235b
B 123a 2 235b
A 123a 3 235b[/tt]

While you state that SalesID is sorted DESC, what about Pick? Is that also sorted in DESC order?



Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[razchip]

Okay, I see what your guys are trying to tell me. As for criteria, from tblOrders, I would want the highest numbered SalesId (235a) and the highest pick letter (C), this should combine with tblPcak where the SalesId is the highest(235b) and the Doc # is the lowest (1), so the result would be 235b C 1. Does this make any sense, or am I still missing the boat?

Thanks for the help.
Greg

 

[SkipVought]

"...highest numbered SalesId (235a) ..."

Does not make sense.

you listed BOTH SalesID's in your example result.

Did you try my SQL?

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[lespaul]

So for EACH salesID you want the highest pick and the lowest doc?

 

[razchip]

As you guys got me thinking, would it be easier to set the criteria, to look at the data, have the SalesID listed in Descending order. Then, go through the records and each time it comes to a new SalesID number, puts an X in new field called Host?

Thanks for the help.
Greg

 

[SkipVought]

The ORDER of SaledID is determined in the Order By Clause, not the CRITERIA.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[lespaul]

Maybe something like this:

SELECT A.SalesID, A.DocID, B.Pick 
FROM
SELECT (SalesID, Min(DocID) As DocID FROM tblPcak GROUP BY SalesID) As A
INNER JOIN (SELECT SalesID, Max(Pick) As Pick FROM tblPcak GROUP BY SalesID) As B on A.SalesID = B.SalesID

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[r937]

from tblOrders, I would want the highest numbered SalesId (235a) and the highest pick letter (C), this should combine with tblPcak where the SalesId is the highest(235b) and the Doc # is the lowest (1), so the result would be 235b C 1.

that's clear enough, except look what happened in this very example of yours -- you threw away 235a!!!


the problem is this: what if the highest numbered SalesID in tblOrders is not the same salesID as the highest numbered SalesID in tblPcak

for this reason, you cannot join the tables, you must UNION them

select SalesID
     , max(tempPick) as Pick
     , max(tempDoc)  as Doc
  from (
       select o.SalesID
            , max(o.Pick)       as tempPick
            , null              as tempDoc 
         from tblOrders as o
        where o.SalesID =
              ( select max(SalesID)
                  from tblOrders )
       group
           by o.SalesID
       UNION ALL
       select p.SalesID
            , null              as tempPick 
            , min(p.Doc)        as tempDoc       
         from tblPcak as p
        where p.SalesID =
              ( select max(SalesID)
                  from tblPcak )
       group
           by p.SalesID
       ) as data
group
    by SalesID

if the highest numbered SalesID in tblOrders is the same salesID as the highest numbered SalesID in tblPcak, this will return one row, otherwise two

r937.com | rudy.ca

 

[razchip]

Thanks, this obtained the information I needed. Thanks again for the help.

Thanks for the help.
Greg