SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Philippines
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hide Duplicate and Get Updated Records

    Hello there,

    anyone here has experience about multiple DISTINCT. because i wan't to get the Updated records. Please refer to my example below.

    Database Records:
    ________Name________________Date________________Cost

    AMD Athlon XP 2400+_____02-12-05 | 09:30 AM______3,900.00
    AMD Sempron 2800+______02-01-05 | 10:00 AM______6,600.00
    AMD Sempron 2800+______02-01-05 | 09:30 AM______6,600.00
    Intel Pentium 4 3.2E GHz __01-05-05 | 11:59 AM_____13,200.00
    Intel Pentium 4 3.2E GHz___02-11-05 | 11:59 AM_____13,200.00


    with this query
    SELECT DISTINCT(cv.name),cv.cost,cv.date_canvass FROM tblcanvass cv WHERE cv.promo <> '' ORDER BY date_canvass");

    it produce same as above results (nothing happen).



    Results Must be:
    ________Name________________Date________________Cost
    AMD Athlon XP 2400+_____02-12-05 | 09:30 AM______3,900.00
    Intel Pentium 4 3.2E GHz___02-11-05 | 11:59 AM_____13,200.00
    AMD Sempron 2800+______02-01-05 | 10:00 AM______6,600.00



    thanks in advance....
    Last edited by james_madrid; Feb 22, 2005 at 01:58.

  2. #2
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try SELECT DISTINCTROW ...

    http://dev.mysql.com/doc/mysql/en/select.html

    oops: i assume you are using mysql...
    leo d.

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Philippines
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes i use MySQL and i use already DISTINCTROW but also nothing happen.

    SELECT DISTINCTROW(cv.name),cv.cost,cv.date_canvass FROM tblcanvass cv WHERE cv.promo <> '' ORDER BY date_canvass");

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could do this:

    SELECT * FROM tblcanvass GROUP BY cv.row

    im pretty sure that would work...

  5. #5
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Philippines
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks but it's not work...

  6. #6
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum. See Where should I post my thread? for details

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select name
         , cost
         , date_canvass 
      from tblcanvass as cv 
     where promo <> '' 
       and date_canvass
         = ( select max(date_canvass)
               from tblcanvass
              where name = cv.name ) 
    order 
        by date_canvass desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Philippines
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still not work. By the way i use MySQL 3.23.49.

    thanks for the effort....

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you are using very outdated software, don't you think it is your responsibility to mention it when asking for help?

    4.1 has been in production since october, so i will assume people have it unless they say otherwise

    Code:
         , curly.cost
         , curly.date_canvass 
      from tblcanvass as curly
    inner
      join tblcanvass as larry
        on curly.name = larry.name
     where curly.promo <> '' 
       and larry.promo <> '' 
    group
        by curly.name
         , curly.cost
         , curly.date_canvass 
    having curly.date_canvass
         = max(larry.date_canvass)
    order 
        by curly.date_canvass desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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