sql syntax help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql syntax help

Author

Topic

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 10:10:57

Don't know if this is possible but here is my statment right now.

SELECT CKIS.MATNR, CKIS.KALNR, CKIS.WERTN
FROM CKIS
WHERE (CKIS.KADKY = '20060901') AND (CKIS.TYPPS = 'M') AND (CKIS.WERKS = '0010')

I need to remove (CKIS.KADKY = '20060901') and somehow comeup with a way to get all records associated to the MAX of KADKY. The tricky part is that I need the result set based of of MATNR not all MATNR's will have the same MAX KADKY

Does that make sense? I'll be here if anybody has questions.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 10:14:51

It will be simpler of you could provide some sample data and your expected output based on the sample data.

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 10:18:24

I'm running some sample data now: I'll post when it comes back
thx

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 12:24:49

Here it is:

MATNR~KADKY
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601

000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301

I just want the records where KADKY is the lagest per MATNR. In this case I would like all MATNR's Where KADKY = '20060601' but I didn't know that 20060601 was the most recent date I need the database to figure that out.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 12:32:40

select matnr, max(kadky) from table1 group by matnr

select kadky, max(matnr) from table1 group by kadky

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 12:37:34

Is that a single statement or 2

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 12:43:48

Where would I put this criteria?
AND (CKIS.TYPPS = 'M') AND (CKIS.WERKS = '0010')

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 13:14:31

I don't think I explanined myself very well. I'll try again.

I DO NOT need the MAX of MATNR found. I need the MAX of KADKY found by MATNR. It's not as easy as asking table CKIS for MAX(KADKY) because there are MATNR groups that will not have the MAX KADKY but I still need the MATNR group with the lastest KADKY.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 13:17:31

select matnr, max(kadky) from table1 where TYPPS = 'M' AND WERKS = '0010' group by matnr

select kadky, max(matnr) from table1 where TYPPS = 'M' AND WERKS = '0010' group by kadky

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 13:26:59

I'll run it the way you have it posted and let you know. Still not sure how that is going to work if you look at my above post.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 13:36:36

Me neither!
I have no access to proper sample data.
I have no idea whatsoever of the output you expect.

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 13:54:07

Is there something more I could give you? The above is a very realistic sample of data.
Let me try to explain it a differant way:

The first Column MATNR has many differant values it is not a key field so you can have like values in that column. KADKY is the same way - not a key field. What I need to do is find all groups of like MATNR's. Find the record for each MATNR group that has the MAX KADKY per MATNR group. Ive adjusted the dataset to match this explaination:

MATNR~KADKY
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060601
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060401
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018415~20060301
000000000000018436~20060401
000000000000018436~20060401
000000000000018436~20060401
000000000000018436~20060401
000000000000018436~20060401
000000000000018436~20060401
000000000000018436~20060301
000000000000018436~20060301
000000000000018436~20060301
000000000000018436~20060301
000000000000018436~20060301
000000000000018436~20060301

Please don't be offened. It was not written as such - just trying to understand. Thank you for all your help Peter.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 14:40:38

And what is your expected output based on the supplied sample data above?

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-15 : 15:15:24

Yes there are 2 additional fields that are in that row I need to capture WERTN and TYPPS. I ran the sql below and it appeared to work per my request. There is another step after to this but I will see if I can get it myself

SELECT MATNR, max(KADKY)
FROM CKIS
WHERE TYPPS = 'M' AND WERKS = '0010'
GROUP BY MATNR
SELECT KADKY, max(MATNR)
FROM CKIS
WHERE TYPPS = 'M' AND WERKS = '0010'
GROUP BY KADKY

So we will see what this returns:

SELECT MATNR, WERTN, TYPPS, max(KADKY)
FROM CKIS
WHERE WERKS = '0010' AND (CKIS.BAUGR NOT IN ('X'))
GROUP BY MATNR, WERTN, TYPPS
SELECT KADKY, max(MATNR)
FROM CKIS
WHERE WERKS = '0010' AND (CKIS.BAUGR NOT IN ('X'))
GROUP BY KADKY

MCP, MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources