Pulling first occurence from Database 1

[mrshardee2]

I have a field entitled "UOM" that I am pulling into Crystal 8.5 from an SQL2000 database. This UOM could be Box, Each, Case, etc. How do I tell Crystal to only choose the first instance for each item number? For example, if the first UOM for Item 37495 is Box, select only that instance.

Any help is greatly appreciated.

Thanks!
Amber

 

[lyanch]

Define first... first by id? First one random, latest date? etc?

Database type and crystal version will help as well

Lisa

 

[mrshardee2]

[ul]
[li]Crystal 8.5 from an SQL2000 database[/li]
[/ul]

I want to select the first UOM in the database for each item number.

Amber

 

[lyanch]

Again... what makes it first? lowest Id number.. highest Id number? Fill in the blank:

First time it happens when I sort by _______________.

Lisa

 

[mrshardee2]

I am sorting ascending by item number.

Amber

 

[Turkbear]

Hi,
Try something like:

Group on Item
Use a Sql Expression in the Details to get the TOP(UOM) (or whatever SqlServer uses to get the First of a set )

 

[lbass]

You still haven't explained what orders UOM within the item number. Without knowing that, the simplest method would be to group on item number and then drag all detail fields into the group header, assuming that you have sorted your fields so that what you consider to be the first record is in first position.

-LB

 

[mrshardee2]

There is no order to the UOM. The values in this field occur randomly. I just want to pull the value of the UOM for the first occurrence in the database of each item number.

Amber

 

[Turkbear]

Hi,
There is a basic confusion here..
There is no 'First' occurrence of that data unless you timestamp when it was inserted...

 

[thorny00]

When writing a SQL statement I use the following to get the the latest date.

SELECT fieldname, MAX(DATES)
FROM tablename
GROUP BY fieldname, DATES

the fieldname could be an loan number and the DATES field is a date when an occurance to place.

Hope this helps!

 

[Turkbear]

Hi, Given what has been posted about the data in Amber's situation, MAX would not return the hypothetical 'first' occurance..


The item # can have many UOM records ( Box, Each, Case)

Which is the MAX(UOM)?