Results 1 to 12 of 12

Thread: Select DISTINCT

  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Select DISTINCT

    Dear guys

    This forum has bee very useful to me. However i have this problem which i dont understand at all. I have an access database, where i call a field to load on formload on a vb.net form. But when i use Distinct keyword to make sure only this field is called, the first record does not appear.

    This code will work but it will bring all records
    Code:
    Dim cent As String = "7"
    Dim cmd As New OleDbCommand
    con.OpenConnection()
    cmd.CommandText = "Select *From from Cand2009 where CentCode like '" & cent & "%'"
    cmd.Connection = con.con
    dr = cmd.ExecuteReader
    dr.Read()
    While (dr.Read)
    SchoolCode = CboCentCode.Items.Add(dr("CentCode"))
    End While



    This code will not bring the first record
    Dim cent As String = "7"

    Dim cmd As New OleDbCommand
    con.OpenConnection()
    cmd.CommandText = "Select DISTINCT CentCode from Cand2009" ' where CentCode like '" & cent & "%'"
    cmd.Connection = con.con
    dr = cmd.ExecuteReader
    dr.Read()
    While (dr.Read)
    SchoolCode = CboCentCode.Items.Add(dr("CentCode"))
    End While
    dr.Close()
    con.CloseConnection()


    Can anybody help cos i dont know why its behaving like this.
    Thanks



    dr.Close()
    con.CloseConnection()

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    distinct returns distinct values. Depending what you mean by "first", you want to use the MIN or FIRST aggregate functions. Have a look at MIN on google, and try using FIRST if that does not get the result you want.

    Failing all that, show us what is returned by your DISTINCT query, and tell us what you actually want.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    Thanks Pootle Flump

    My problem is i have about 2000 records and in this particular field i have field name called centcode, in this i have centcodes which contains records from the same centcode. So why i use Distinct is to prevent one center appearing several times before a user can see the next cent code. So what i mean by the first record is the first cent contains the first record in the table which is not showing.

    Hope am clearer now. Please help

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gilsygirl
    Hope am clearer now.
    Sorry - no

    Please can you show some example data that is in the table, and then show what data you want your query to return.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    14
    You might be better off grouping on your field, or possibly using DISTINCTROW instead.

    To Group you would end up with:

    SELECT CentCode FROM Cand2009 GROUP BY CentCode HAVING CentCode Like '" & cent & "%';"

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Kafrin
    You might be better off grouping on your field
    Without an aggregate function(s), that is semantically exactly the same as distinct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Kafrin
    SELECT CentCode FROM Cand2009 GROUP BY CentCode HAVING CentCode Like '" & cent & "%';"
    eeeeeeewwwwwwwww

    please, do not abuse SELECT statement clauses like that

    change it to:

    SELECT CentCode FROM Cand2009 WHERE CentCode Like '" & cent & "%' GROUP BY CentCode;"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2009
    Posts
    6
    Thanks for the reply. The Group By Centcode is doing the same as the Distinct key word. The first Record still does not appear. I Cannt understand why its behaving this way.
    Will have a look at the tables.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As mentioned, please show us some sample data and then what you would want the final output to look like.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2009
    Posts
    6

    Select Statement and Group By

    This is what i get when i run the program. the first record does not show. Do you have any other suggestion.
    Attached Thumbnails Attached Thumbnails ppppp.JPG  

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't know what's the meaning of "first record" for you. To retrieve a "first record" you must precise why (or how) it is the first and this implies a sort order in the table. The DISTINCt predicate simply guarantees that not two identical rows will be returned but nothing else.

    If you want to retrieve the first record you have then to precise a sort order and your query should be something like this:
    Code:
    "Select TOP 1 *From from Cand2009 where CentCode like '" & cent & "%' ORDER BY <xxx>"
    where <xxx> is the column used to sort the table and determine which record is the first one.

    An entry in the Microsoft Technet site (TOP (Transact-SQL)) precises:
    If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
    Search in Technet (Microsoft SQL Server Library) for TOP, MAX, MIN, ORDER BY and GROUP BY

    Have a nice day!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sinndho
    I don't know what's the meaning of "first record" for you.
    probably because this is the MS Access forum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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