Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: First Occurence of a record.

    Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record. Canwe write the query without using row id ?

    It would be a great help to me.

    Thanks,
    John

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: First Occurence of a record.

    Define what you mean by "first". Just any random record, or the one with the lowest value for some other column? There is no "first" record per se in a relational database, other than when you ORDER BY something.

  3. #3
    Join Date
    Oct 2003
    Posts
    37

    Re: First Occurence of a record.

    Hi John

    If you are selecting the distinct order types why does it matter whether you pick the earliest or the latest?

    Surely you are just interested in the different order types?

    If so try select distinct(order_type) from table_name;

    However, assuming that you do want the earliest record - if you had a list of the rows how would you know the earliest record from the rest?

    Once you know that you could then use a function like min on that column to product the earliest occurances of each order type.


    Regards

    Keith

  4. #4
    Join Date
    Jan 2004
    Posts
    5

    Re: First Occurence of a record.

    Yeah, it can be any first random record.

  5. #5
    Join Date
    Jan 2004
    Posts
    5

    Re: First Occurence of a record.

    Thanks for the information Keith. I need any random first record for that particular order type. Idid use min (rowid) but it did not work

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    as was said earlier, if all you care about is getting all the "distinct" types just

    "select distinct {whatever column} from table"

  7. #7
    Join Date
    Jan 2004
    Posts
    5

    Re: First Occurence of a record.

    Originally posted by johnson100_99
    Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record. Canwe write the query without using row id ?

    It would be a great help to me.

    Thanks,
    John
    Have you looked at using First_Value()? It will allow you to partition data based on the value of one field, optionally sort the data within the partition based on a different field, and select the "First" value of a third field. It's a little bit quirky, but once you get used to it, it can do some fun stuff.
    There is also a converse function called Last_Value()...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the primary key of the table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2003
    Posts
    37

    Re: First Occurence of a record.

    Hi John

    I'm still not really very clear as to what you actually want, but I not imagine that min(rowid) just returned the earliest rowid #

    Try:-

    select order_type, min(rowid)
    from table_name
    group by order_type;


    Regards

    Keith

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: First Occurence of a record.

    Originally posted by kfitzsimmons
    Hi John

    I'm still not really very clear as to what you actually want, but I not imagine that min(rowid) just returned the earliest rowid #

    Try:-

    select order_type, min(rowid)
    from table_name
    group by order_type;


    Regards

    Keith
    And if that is as good a set of records as any, then to get the actual records would be:

    select * from table_name
    where rowid in
    (select min(rowid)
    from table_name
    group by order_type);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •