Link to home
Start Free TrialLog in
Avatar of jmto
jmto

asked on

Simple Query

I am pretty much SQL illiterate and I need help to put this query together:

First my Data:
ID      FORMATTEDLOTNUMBER      SOURCECOMPANYKEY      Expr1
11      LT10-00002                       1435      280214235
12      LT10-00002.1      1292      280214235
13      LT10-00002.2      1292      280214235
14      LT10-00002.4      1292      280214235
15      LT10-00002.3      1292      280214235
16      LT10-00002.6      1292      280214235
17      LT10-00002.5      1292      280214235
18      LT10-00002.7      1292      280214235
19      LT10-00086                       1293      N/A
20      LT10-00086                           1292      NA
21      LT10-00087         1293      N/A
22      LT10-00087        1292      NA


I need to be able to query EVERY FIELD with the first instance of every distinct first 10 characters of the FORMATTEDLOTNUMBER, i.e., row 11, 19, 21.

Thanks

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Access or Oracle?  It was cross posted in both zones.

try:

select * from (
select t.*, row_number() over (partition by FORMATTEDLOTNUMBER order by ID) rn
from mytable t
) x where rn=1
select substr(formatted_lot_number,1,10) from your table
Avatar of jmto

ASKER

This is really a query from a Oracle database, using Access and ODBC.

Hainkurt, what is row_number() supposed to be ?
Avatar of jmto

ASKER

anumoses, the problem is not the substr, the problem is querying full rows for the first distinct substr(formatted_lot_number,1,10)
row_number is an Oracle analytic function.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm

I doubt you can use that with ODBC.  Try taking HainKurt's select and create a view in Oracle then select that view from Access.
Avatar of jmto

ASKER

row_number is undertood. That would have been a great choice, but you are right, it doesn't seem to be available through ODBC, and this is a vendor only administrered system not allowing me to create anything additional in Oracle.

Any luck with another approach ?
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you want all the data for those records, then try:

SELECT *
FROM yourTable
INNER JOIN (SELECT MIN(yourTable.ID) as ID FROM yourTable GROUP BY Left(FormattedLotNumber, 10)) as T
ON yourTable.ID = T.ID
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HainKurt,

Your Group By clause needs to specify LEFT(FORMATTEDLOTNUMBER, 10), and since ID appears to be unique, you don't really need to include the FormattedLotNumber in the SELECT portion of the subquery or in the WHERE clause.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jmto

ASKER

Thank you all for you quick answers. I combined them all to the following:

SELECT * FROM CORR_RECEIVED_PARTS
WHERE ID in (
SELECT Min(ID)
FROM CORR_RECEIVED_PARTS
GROUP BY Left(FORMATTEDLOTNUMBER, 10)
);

It did the job.