Select Distinct


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Select Distinct

  1. #1
    Cary R Guest

    Select Distinct


    I've noticed there's been a lot of questions on the select distinct statement,
    and in many tries, I can't figure out how to use this correctly in my scenario.

    I currently have 2 DBs - one that lists products and one that lists all items
    that have been signed out (or in) at any time. This is for inventory purposes.
    I am trying to create an HTML table that lists each of the items only once
    with the last time they've been signed out/in or blank if they've never been.

    My code currently is
    SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS, SIGNOUT.RACFID,
    SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN WEBSRV.EQUIPMNT
    ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    ORDER BY SIGNOUT.DATEOUT DESC

    This code still lists items that have been signed out/in more than once,
    multiple times. I've tried using sub-queries, but I'm not quite sure of
    the exact syntax to make this work correctly.

    Hopefully this is enough information. Thanks to anyone who can help!
    Cary


  2. #2
    Daniel Reber Guest

    Re: Select Distinct

    try this:

    select
    e.serialnum,
    e.[name]
    s.status,
    s.racfid,
    s.dateout,
    s.datein,
    from equipmnt
    right join (select
    max(dateout) dateout,
    serialnum,
    status,
    racfid,
    datein
    from signout
    group by serialnum,racfid,
    status, datein ) s on s.serialnum = e.serialnum
    order by dateout desc

    you may have to change the group by in the derived table to get the data the
    way you want it.

    HTH

    Daniel Reber
    Datamasters, Inc

    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3bab6314$1@news.devx.com...
    >
    > I've noticed there's been a lot of questions on the select distinct

    statement,
    > and in many tries, I can't figure out how to use this correctly in my

    scenario.
    >
    > I currently have 2 DBs - one that lists products and one that lists all

    items
    > that have been signed out (or in) at any time. This is for inventory

    purposes.
    > I am trying to create an HTML table that lists each of the items only

    once
    > with the last time they've been signed out/in or blank if they've never

    been.
    >
    > My code currently is
    > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,

    SIGNOUT.RACFID,
    > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN

    WEBSRV.EQUIPMNT
    > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > ORDER BY SIGNOUT.DATEOUT DESC
    >
    > This code still lists items that have been signed out/in more than once,
    > multiple times. I've tried using sub-queries, but I'm not quite sure of
    > the exact syntax to make this work correctly.
    >
    > Hopefully this is enough information. Thanks to anyone who can help!
    > Cary
    >




  3. #3
    Daniel Reber Guest

    Re: Select Distinct

    I forgot the comma after e.[name]

    sorry.

    "Daniel Reber" <dreber@dminconline.com> wrote in message
    news:3bab6af0@news.devx.com...
    > try this:
    >
    > select
    > e.serialnum,
    > e.[name]
    > s.status,
    > s.racfid,
    > s.dateout,
    > s.datein,
    > from equipmnt
    > right join (select
    > max(dateout) dateout,
    > serialnum,
    > status,
    > racfid,
    > datein
    > from signout
    > group by serialnum,racfid,
    > status, datein ) s on s.serialnum = e.serialnum
    > order by dateout desc
    >
    > you may have to change the group by in the derived table to get the data

    the
    > way you want it.
    >
    > HTH
    >
    > Daniel Reber
    > Datamasters, Inc
    >
    > "Cary R" <cary.rotman@intria.com> wrote in message
    > news:3bab6314$1@news.devx.com...
    > >
    > > I've noticed there's been a lot of questions on the select distinct

    > statement,
    > > and in many tries, I can't figure out how to use this correctly in my

    > scenario.
    > >
    > > I currently have 2 DBs - one that lists products and one that lists all

    > items
    > > that have been signed out (or in) at any time. This is for inventory

    > purposes.
    > > I am trying to create an HTML table that lists each of the items only

    > once
    > > with the last time they've been signed out/in or blank if they've never

    > been.
    > >
    > > My code currently is
    > > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,

    > SIGNOUT.RACFID,
    > > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN

    > WEBSRV.EQUIPMNT
    > > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > > ORDER BY SIGNOUT.DATEOUT DESC
    > >
    > > This code still lists items that have been signed out/in more than once,
    > > multiple times. I've tried using sub-queries, but I'm not quite sure of
    > > the exact syntax to make this work correctly.
    > >
    > > Hopefully this is enough information. Thanks to anyone who can help!
    > > Cary
    > >

    >
    >




  4. #4
    Daniel Reber Guest

    Re: Select Distinct

    I also added a comma after s.datein by mistake.
    I think that I need more coffee.


    "Daniel Reber" <dreber@dminconline.com> wrote in message
    news:3bab6af0@news.devx.com...
    > try this:
    >
    > select
    > e.serialnum,
    > e.[name]
    > s.status,
    > s.racfid,
    > s.dateout,
    > s.datein,
    > from equipmnt
    > right join (select
    > max(dateout) dateout,
    > serialnum,
    > status,
    > racfid,
    > datein
    > from signout
    > group by serialnum,racfid,
    > status, datein ) s on s.serialnum = e.serialnum
    > order by dateout desc
    >
    > you may have to change the group by in the derived table to get the data

    the
    > way you want it.
    >
    > HTH
    >
    > Daniel Reber
    > Datamasters, Inc
    >
    > "Cary R" <cary.rotman@intria.com> wrote in message
    > news:3bab6314$1@news.devx.com...
    > >
    > > I've noticed there's been a lot of questions on the select distinct

    > statement,
    > > and in many tries, I can't figure out how to use this correctly in my

    > scenario.
    > >
    > > I currently have 2 DBs - one that lists products and one that lists all

    > items
    > > that have been signed out (or in) at any time. This is for inventory

    > purposes.
    > > I am trying to create an HTML table that lists each of the items only

    > once
    > > with the last time they've been signed out/in or blank if they've never

    > been.
    > >
    > > My code currently is
    > > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,

    > SIGNOUT.RACFID,
    > > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN

    > WEBSRV.EQUIPMNT
    > > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > > ORDER BY SIGNOUT.DATEOUT DESC
    > >
    > > This code still lists items that have been signed out/in more than once,
    > > multiple times. I've tried using sub-queries, but I'm not quite sure of
    > > the exact syntax to make this work correctly.
    > >
    > > Hopefully this is enough information. Thanks to anyone who can help!
    > > Cary
    > >

    >
    >




  5. #5
    Cary R Guest

    Re: Select Distinct


    Thanx a lot for the quick response, however I'm still facing the same problems.


    My main problem still exists in the duplication of data. For example, say
    there are items A, B, C, D within the equipment database. Now, item A and
    B have both been signed out, they appear fine. However, say C has been signed
    out, signed in, then signed back out again, all 3 occurences of the C item
    signout are visible on the page. (By changing Right Join to Full outer join,
    I was able to see the D item with the table left blank as desired - I don't
    know why the right join didn't work this time.)

    My goal is to somehow use the distinct command to only show the last occurence
    of each item. It is very likely that your code was really close, however
    I've been workin on this one line for a while now, and I seem to always end
    up with the same result.

    Thanx again,
    Cary


    "Daniel Reber" <dreber@dminconline.com> wrote:
    >I also added a comma after s.datein by mistake.
    >I think that I need more coffee.
    >
    >
    >"Daniel Reber" <dreber@dminconline.com> wrote in message
    >news:3bab6af0@news.devx.com...
    >> try this:
    >>
    >> select
    >> e.serialnum,
    >> e.[name]
    >> s.status,
    >> s.racfid,
    >> s.dateout,
    >> s.datein,
    >> from equipmnt
    >> right join (select
    >> max(dateout) dateout,
    >> serialnum,
    >> status,
    >> racfid,
    >> datein
    >> from signout
    >> group by serialnum,racfid,
    >> status, datein ) s on s.serialnum = e.serialnum
    >> order by dateout desc
    >>
    >> you may have to change the group by in the derived table to get the data

    >the
    >> way you want it.
    >>
    >> HTH
    >>
    >> Daniel Reber
    >> Datamasters, Inc
    >>
    >> "Cary R" <cary.rotman@intria.com> wrote in message
    >> news:3bab6314$1@news.devx.com...
    >> >
    >> > I've noticed there's been a lot of questions on the select distinct

    >> statement,
    >> > and in many tries, I can't figure out how to use this correctly in my

    >> scenario.
    >> >
    >> > I currently have 2 DBs - one that lists products and one that lists

    all
    >> items
    >> > that have been signed out (or in) at any time. This is for inventory

    >> purposes.
    >> > I am trying to create an HTML table that lists each of the items only

    >> once
    >> > with the last time they've been signed out/in or blank if they've never

    >> been.
    >> >
    >> > My code currently is
    >> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,

    >> SIGNOUT.RACFID,
    >> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN

    >> WEBSRV.EQUIPMNT
    >> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >> > ORDER BY SIGNOUT.DATEOUT DESC
    >> >
    >> > This code still lists items that have been signed out/in more than once,
    >> > multiple times. I've tried using sub-queries, but I'm not quite sure

    of
    >> > the exact syntax to make this work correctly.
    >> >
    >> > Hopefully this is enough information. Thanks to anyone who can help!
    >> > Cary
    >> >

    >>
    >>

    >
    >



  6. #6
    Daniel Reber Guest

    Re: Select Distinct

    what is the unique id for the signout table?

    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3bab8599$1@news.devx.com...
    >
    > Thanx a lot for the quick response, however I'm still facing the same

    problems.
    >
    >
    > My main problem still exists in the duplication of data. For example, say
    > there are items A, B, C, D within the equipment database. Now, item A and
    > B have both been signed out, they appear fine. However, say C has been

    signed
    > out, signed in, then signed back out again, all 3 occurences of the C item
    > signout are visible on the page. (By changing Right Join to Full outer

    join,
    > I was able to see the D item with the table left blank as desired - I

    don't
    > know why the right join didn't work this time.)
    >
    > My goal is to somehow use the distinct command to only show the last

    occurence
    > of each item. It is very likely that your code was really close, however
    > I've been workin on this one line for a while now, and I seem to always

    end
    > up with the same result.
    >
    > Thanx again,
    > Cary
    >
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote:
    > >I also added a comma after s.datein by mistake.
    > >I think that I need more coffee.
    > >
    > >
    > >"Daniel Reber" <dreber@dminconline.com> wrote in message
    > >news:3bab6af0@news.devx.com...
    > >> try this:
    > >>
    > >> select
    > >> e.serialnum,
    > >> e.[name]
    > >> s.status,
    > >> s.racfid,
    > >> s.dateout,
    > >> s.datein,
    > >> from equipmnt
    > >> right join (select
    > >> max(dateout) dateout,
    > >> serialnum,
    > >> status,
    > >> racfid,
    > >> datein
    > >> from signout
    > >> group by serialnum,racfid,
    > >> status, datein ) s on s.serialnum = e.serialnum
    > >> order by dateout desc
    > >>
    > >> you may have to change the group by in the derived table to get the

    data
    > >the
    > >> way you want it.
    > >>
    > >> HTH
    > >>
    > >> Daniel Reber
    > >> Datamasters, Inc
    > >>
    > >> "Cary R" <cary.rotman@intria.com> wrote in message
    > >> news:3bab6314$1@news.devx.com...
    > >> >
    > >> > I've noticed there's been a lot of questions on the select distinct
    > >> statement,
    > >> > and in many tries, I can't figure out how to use this correctly in my
    > >> scenario.
    > >> >
    > >> > I currently have 2 DBs - one that lists products and one that lists

    > all
    > >> items
    > >> > that have been signed out (or in) at any time. This is for inventory
    > >> purposes.
    > >> > I am trying to create an HTML table that lists each of the items

    only
    > >> once
    > >> > with the last time they've been signed out/in or blank if they've

    never
    > >> been.
    > >> >
    > >> > My code currently is
    > >> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
    > >> SIGNOUT.RACFID,
    > >> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    > >> WEBSRV.EQUIPMNT
    > >> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > >> > ORDER BY SIGNOUT.DATEOUT DESC
    > >> >
    > >> > This code still lists items that have been signed out/in more than

    once,
    > >> > multiple times. I've tried using sub-queries, but I'm not quite sure

    > of
    > >> > the exact syntax to make this work correctly.
    > >> >
    > >> > Hopefully this is enough information. Thanks to anyone who can help!
    > >> > Cary
    > >> >
    > >>
    > >>

    > >
    > >

    >




  7. #7
    Cary R Guest

    Re: Select Distinct



    >
    >Thanx a lot for the quick response, however I'm still facing the same problems.
    >
    >
    >My main problem still exists in the duplication of data. For example, say
    >there are items A, B, C, D within the equipment database. Now, item A and
    >B have both been signed out, they appear fine. However, say C has been

    signed
    >out, signed in, then signed back out again, all 3 occurences of the C item
    >signout are visible on the page. (By changing Right Join to Full outer

    join,
    >I was able to see the D item with the table left blank as desired - I don't
    >know why the right join didn't work this time.)
    >
    >My goal is to somehow use the distinct command to only show the last occurence
    >of each item. It is very likely that your code was really close, however
    >I've been workin on this one line for a while now, and I seem to always

    end up with the same result.

    Maybe, to make it more simple...If I were just to type: select distinct e.serialnum,
    then it shows up correctly (obviously without other data), however then I
    type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
    distinct command no longer functions as I want it to.
    >
    >Thanx again,
    >Cary
    >
    >
    >"Daniel Reber" <dreber@dminconline.com> wrote:
    >>I also added a comma after s.datein by mistake.
    >>I think that I need more coffee.
    >>
    >>
    >>"Daniel Reber" <dreber@dminconline.com> wrote in message
    >>news:3bab6af0@news.devx.com...
    >>> try this:
    >>>
    >>> select
    >>> e.serialnum,
    >>> e.[name]
    >>> s.status,
    >>> s.racfid,
    >>> s.dateout,
    >>> s.datein,
    >>> from equipmnt
    >>> right join (select
    >>> max(dateout) dateout,
    >>> serialnum,
    >>> status,
    >>> racfid,
    >>> datein
    >>> from signout
    >>> group by serialnum,racfid,
    >>> status, datein ) s on s.serialnum = e.serialnum
    >>> order by dateout desc
    >>>
    >>> you may have to change the group by in the derived table to get the data

    >>the
    >>> way you want it.
    >>>
    >>> HTH
    >>>
    >>> Daniel Reber
    >>> Datamasters, Inc
    >>>
    >>> "Cary R" <cary.rotman@intria.com> wrote in message
    >>> news:3bab6314$1@news.devx.com...
    >>> >
    >>> > I've noticed there's been a lot of questions on the select distinct
    >>> statement,
    >>> > and in many tries, I can't figure out how to use this correctly in

    my
    >>> scenario.
    >>> >
    >>> > I currently have 2 DBs - one that lists products and one that lists

    >all
    >>> items
    >>> > that have been signed out (or in) at any time. This is for inventory
    >>> purposes.
    >>> > I am trying to create an HTML table that lists each of the items only
    >>> once
    >>> > with the last time they've been signed out/in or blank if they've never
    >>> been.
    >>> >
    >>> > My code currently is
    >>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
    >>> SIGNOUT.RACFID,
    >>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    >>> WEBSRV.EQUIPMNT
    >>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >>> > ORDER BY SIGNOUT.DATEOUT DESC
    >>> >
    >>> > This code still lists items that have been signed out/in more than

    once,
    >>> > multiple times. I've tried using sub-queries, but I'm not quite sure

    >of
    >>> > the exact syntax to make this work correctly.
    >>> >
    >>> > Hopefully this is enough information. Thanks to anyone who can help!
    >>> > Cary
    >>> >
    >>>
    >>>

    >>
    >>

    >



  8. #8
    Cary R Guest

    Re: Select Distinct


    Serialnum for both

    "Cary R" <cary.rotman@intria.com> wrote:
    >
    >
    >>
    >>Thanx a lot for the quick response, however I'm still facing the same problems.
    >>
    >>
    >>My main problem still exists in the duplication of data. For example,

    say
    >>there are items A, B, C, D within the equipment database. Now, item A and
    >>B have both been signed out, they appear fine. However, say C has been

    >signed
    >>out, signed in, then signed back out again, all 3 occurences of the C item
    >>signout are visible on the page. (By changing Right Join to Full outer

    >join,
    >>I was able to see the D item with the table left blank as desired - I don't
    >>know why the right join didn't work this time.)
    >>
    >>My goal is to somehow use the distinct command to only show the last occurence
    >>of each item. It is very likely that your code was really close, however
    >>I've been workin on this one line for a while now, and I seem to always

    >end up with the same result.
    >
    >Maybe, to make it more simple...If I were just to type: select distinct

    e.serialnum,
    >then it shows up correctly (obviously without other data), however then

    I
    >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
    >distinct command no longer functions as I want it to.
    >>
    >>Thanx again,
    >>Cary
    >>
    >>
    >>"Daniel Reber" <dreber@dminconline.com> wrote:
    >>>I also added a comma after s.datein by mistake.
    >>>I think that I need more coffee.
    >>>
    >>>
    >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    >>>news:3bab6af0@news.devx.com...
    >>>> try this:
    >>>>
    >>>> select
    >>>> e.serialnum,
    >>>> e.[name]
    >>>> s.status,
    >>>> s.racfid,
    >>>> s.dateout,
    >>>> s.datein,
    >>>> from equipmnt
    >>>> right join (select
    >>>> max(dateout) dateout,
    >>>> serialnum,
    >>>> status,
    >>>> racfid,
    >>>> datein
    >>>> from signout
    >>>> group by serialnum,racfid,
    >>>> status, datein ) s on s.serialnum = e.serialnum
    >>>> order by dateout desc
    >>>>
    >>>> you may have to change the group by in the derived table to get the

    data
    >>>the
    >>>> way you want it.
    >>>>
    >>>> HTH
    >>>>
    >>>> Daniel Reber
    >>>> Datamasters, Inc
    >>>>
    >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    >>>> news:3bab6314$1@news.devx.com...
    >>>> >
    >>>> > I've noticed there's been a lot of questions on the select distinct
    >>>> statement,
    >>>> > and in many tries, I can't figure out how to use this correctly in

    >my
    >>>> scenario.
    >>>> >
    >>>> > I currently have 2 DBs - one that lists products and one that lists

    >>all
    >>>> items
    >>>> > that have been signed out (or in) at any time. This is for inventory
    >>>> purposes.
    >>>> > I am trying to create an HTML table that lists each of the items

    only
    >>>> once
    >>>> > with the last time they've been signed out/in or blank if they've

    never
    >>>> been.
    >>>> >
    >>>> > My code currently is
    >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
    >>>> SIGNOUT.RACFID,
    >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    >>>> WEBSRV.EQUIPMNT
    >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    >>>> >
    >>>> > This code still lists items that have been signed out/in more than

    >once,
    >>>> > multiple times. I've tried using sub-queries, but I'm not quite sure

    >>of
    >>>> > the exact syntax to make this work correctly.
    >>>> >
    >>>> > Hopefully this is enough information. Thanks to anyone who can help!
    >>>> > Cary
    >>>> >
    >>>>
    >>>>
    >>>
    >>>

    >>

    >



  9. #9
    Daniel Reber Guest

    Re: Select Distinct

    how can that be the unique id for signout if the serialnum is there multiple
    times?

    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3bab884b$1@news.devx.com...
    >
    > Serialnum for both
    >
    > "Cary R" <cary.rotman@intria.com> wrote:
    > >
    > >
    > >>
    > >>Thanx a lot for the quick response, however I'm still facing the same

    problems.
    > >>
    > >>
    > >>My main problem still exists in the duplication of data. For example,

    > say
    > >>there are items A, B, C, D within the equipment database. Now, item A

    and
    > >>B have both been signed out, they appear fine. However, say C has been

    > >signed
    > >>out, signed in, then signed back out again, all 3 occurences of the C

    item
    > >>signout are visible on the page. (By changing Right Join to Full outer

    > >join,
    > >>I was able to see the D item with the table left blank as desired - I

    don't
    > >>know why the right join didn't work this time.)
    > >>
    > >>My goal is to somehow use the distinct command to only show the last

    occurence
    > >>of each item. It is very likely that your code was really close,

    however
    > >>I've been workin on this one line for a while now, and I seem to always

    > >end up with the same result.
    > >
    > >Maybe, to make it more simple...If I were just to type: select distinct

    > e.serialnum,
    > >then it shows up correctly (obviously without other data), however then

    > I
    > >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
    > >distinct command no longer functions as I want it to.
    > >>
    > >>Thanx again,
    > >>Cary
    > >>
    > >>
    > >>"Daniel Reber" <dreber@dminconline.com> wrote:
    > >>>I also added a comma after s.datein by mistake.
    > >>>I think that I need more coffee.
    > >>>
    > >>>
    > >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    > >>>news:3bab6af0@news.devx.com...
    > >>>> try this:
    > >>>>
    > >>>> select
    > >>>> e.serialnum,
    > >>>> e.[name]
    > >>>> s.status,
    > >>>> s.racfid,
    > >>>> s.dateout,
    > >>>> s.datein,
    > >>>> from equipmnt
    > >>>> right join (select
    > >>>> max(dateout) dateout,
    > >>>> serialnum,
    > >>>> status,
    > >>>> racfid,
    > >>>> datein
    > >>>> from signout
    > >>>> group by serialnum,racfid,
    > >>>> status, datein ) s on s.serialnum = e.serialnum
    > >>>> order by dateout desc
    > >>>>
    > >>>> you may have to change the group by in the derived table to get the

    > data
    > >>>the
    > >>>> way you want it.
    > >>>>
    > >>>> HTH
    > >>>>
    > >>>> Daniel Reber
    > >>>> Datamasters, Inc
    > >>>>
    > >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    > >>>> news:3bab6314$1@news.devx.com...
    > >>>> >
    > >>>> > I've noticed there's been a lot of questions on the select distinct
    > >>>> statement,
    > >>>> > and in many tries, I can't figure out how to use this correctly in

    > >my
    > >>>> scenario.
    > >>>> >
    > >>>> > I currently have 2 DBs - one that lists products and one that lists
    > >>all
    > >>>> items
    > >>>> > that have been signed out (or in) at any time. This is for

    inventory
    > >>>> purposes.
    > >>>> > I am trying to create an HTML table that lists each of the items

    > only
    > >>>> once
    > >>>> > with the last time they've been signed out/in or blank if they've

    > never
    > >>>> been.
    > >>>> >
    > >>>> > My code currently is
    > >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
    > >>>> SIGNOUT.RACFID,
    > >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    > >>>> WEBSRV.EQUIPMNT
    > >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    > >>>> >
    > >>>> > This code still lists items that have been signed out/in more than

    > >once,
    > >>>> > multiple times. I've tried using sub-queries, but I'm not quite

    sure
    > >>of
    > >>>> > the exact syntax to make this work correctly.
    > >>>> >
    > >>>> > Hopefully this is enough information. Thanks to anyone who can

    help!
    > >>>> > Cary
    > >>>> >
    > >>>>
    > >>>>
    > >>>
    > >>>
    > >>

    > >

    >




  10. #10
    Cary R Guest

    Re: Select Distinct


    Oh...sorry about that.
    It's actually a signoutID which is formed by concatenating the serial number,
    username, and the signoutdate. (That actually brings up a flaw in that if
    someone signs an object in and out on the same day, there will be some problems,
    so thank your for bringing that up.)

    But assuming that doesn't happen, will that help at all??



    "Daniel Reber" <dreber@dminconline.com> wrote:
    >how can that be the unique id for signout if the serialnum is there multiple
    >times?
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3bab884b$1@news.devx.com...
    >>
    >> Serialnum for both
    >>
    >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >
    >> >
    >> >>
    >> >>Thanx a lot for the quick response, however I'm still facing the same

    >problems.
    >> >>
    >> >>
    >> >>My main problem still exists in the duplication of data. For example,

    >> say
    >> >>there are items A, B, C, D within the equipment database. Now, item

    A
    >and
    >> >>B have both been signed out, they appear fine. However, say C has been
    >> >signed
    >> >>out, signed in, then signed back out again, all 3 occurences of the

    C
    >item
    >> >>signout are visible on the page. (By changing Right Join to Full outer
    >> >join,
    >> >>I was able to see the D item with the table left blank as desired -

    I
    >don't
    >> >>know why the right join didn't work this time.)
    >> >>
    >> >>My goal is to somehow use the distinct command to only show the last

    >occurence
    >> >>of each item. It is very likely that your code was really close,

    >however
    >> >>I've been workin on this one line for a while now, and I seem to always
    >> >end up with the same result.
    >> >
    >> >Maybe, to make it more simple...If I were just to type: select distinct

    >> e.serialnum,
    >> >then it shows up correctly (obviously without other data), however then

    >> I
    >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on,

    the
    >> >distinct command no longer functions as I want it to.
    >> >>
    >> >>Thanx again,
    >> >>Cary
    >> >>
    >> >>
    >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
    >> >>>I also added a comma after s.datein by mistake.
    >> >>>I think that I need more coffee.
    >> >>>
    >> >>>
    >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    >> >>>news:3bab6af0@news.devx.com...
    >> >>>> try this:
    >> >>>>
    >> >>>> select
    >> >>>> e.serialnum,
    >> >>>> e.[name]
    >> >>>> s.status,
    >> >>>> s.racfid,
    >> >>>> s.dateout,
    >> >>>> s.datein,
    >> >>>> from equipmnt
    >> >>>> right join (select
    >> >>>> max(dateout) dateout,
    >> >>>> serialnum,
    >> >>>> status,
    >> >>>> racfid,
    >> >>>> datein
    >> >>>> from signout
    >> >>>> group by serialnum,racfid,
    >> >>>> status, datein ) s on s.serialnum = e.serialnum
    >> >>>> order by dateout desc
    >> >>>>
    >> >>>> you may have to change the group by in the derived table to get the

    >> data
    >> >>>the
    >> >>>> way you want it.
    >> >>>>
    >> >>>> HTH
    >> >>>>
    >> >>>> Daniel Reber
    >> >>>> Datamasters, Inc
    >> >>>>
    >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    >> >>>> news:3bab6314$1@news.devx.com...
    >> >>>> >
    >> >>>> > I've noticed there's been a lot of questions on the select distinct
    >> >>>> statement,
    >> >>>> > and in many tries, I can't figure out how to use this correctly

    in
    >> >my
    >> >>>> scenario.
    >> >>>> >
    >> >>>> > I currently have 2 DBs - one that lists products and one that lists
    >> >>all
    >> >>>> items
    >> >>>> > that have been signed out (or in) at any time. This is for

    >inventory
    >> >>>> purposes.
    >> >>>> > I am trying to create an HTML table that lists each of the items

    >> only
    >> >>>> once
    >> >>>> > with the last time they've been signed out/in or blank if they've

    >> never
    >> >>>> been.
    >> >>>> >
    >> >>>> > My code currently is
    >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
    >> >>>> SIGNOUT.RACFID,
    >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    >> >>>> WEBSRV.EQUIPMNT
    >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    >> >>>> >
    >> >>>> > This code still lists items that have been signed out/in more than
    >> >once,
    >> >>>> > multiple times. I've tried using sub-queries, but I'm not quite

    >sure
    >> >>of
    >> >>>> > the exact syntax to make this work correctly.
    >> >>>> >
    >> >>>> > Hopefully this is enough information. Thanks to anyone who can

    >help!
    >> >>>> > Cary
    >> >>>> >
    >> >>>>
    >> >>>>
    >> >>>
    >> >>>
    >> >>
    >> >

    >>

    >
    >



  11. #11
    Daniel Reber Guest

    Re: Select Distinct

    you might want to consider changing that to an auto number. I would make
    things easier.
    the reason why I asked is so we can change the query to

    select
    e.serialnum,
    e.[name],
    s2.status,
    s2.racfid,
    s1.dateout,
    s2.datein
    from equipmnt
    right join (select
    max(dateout) dateout, uniqueID
    from signout
    group by uniqueID) s1 s on s1.serialnum = e.serialnum
    right join (select
    uniqueID,
    serialnum,
    status,
    racfid,
    datein
    from signout ) s2 on s2.uniqueID= s1.uniqueID
    order by s1.dateout desc


    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3bab8a53$1@news.devx.com...
    >
    > Oh...sorry about that.
    > It's actually a signoutID which is formed by concatenating the serial

    number,
    > username, and the signoutdate. (That actually brings up a flaw in that if
    > someone signs an object in and out on the same day, there will be some

    problems,
    > so thank your for bringing that up.)
    >
    > But assuming that doesn't happen, will that help at all??
    >
    >
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote:
    > >how can that be the unique id for signout if the serialnum is there

    multiple
    > >times?
    > >
    > >"Cary R" <cary.rotman@intria.com> wrote in message
    > >news:3bab884b$1@news.devx.com...
    > >>
    > >> Serialnum for both
    > >>
    > >> "Cary R" <cary.rotman@intria.com> wrote:
    > >> >
    > >> >
    > >> >>
    > >> >>Thanx a lot for the quick response, however I'm still facing the same

    > >problems.
    > >> >>
    > >> >>
    > >> >>My main problem still exists in the duplication of data. For

    example,
    > >> say
    > >> >>there are items A, B, C, D within the equipment database. Now, item

    > A
    > >and
    > >> >>B have both been signed out, they appear fine. However, say C has

    been
    > >> >signed
    > >> >>out, signed in, then signed back out again, all 3 occurences of the

    > C
    > >item
    > >> >>signout are visible on the page. (By changing Right Join to Full

    outer
    > >> >join,
    > >> >>I was able to see the D item with the table left blank as desired -

    > I
    > >don't
    > >> >>know why the right join didn't work this time.)
    > >> >>
    > >> >>My goal is to somehow use the distinct command to only show the last

    > >occurence
    > >> >>of each item. It is very likely that your code was really close,

    > >however
    > >> >>I've been workin on this one line for a while now, and I seem to

    always
    > >> >end up with the same result.
    > >> >
    > >> >Maybe, to make it more simple...If I were just to type: select

    distinct
    > >> e.serialnum,
    > >> >then it shows up correctly (obviously without other data), however

    then
    > >> I
    > >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on,

    > the
    > >> >distinct command no longer functions as I want it to.
    > >> >>
    > >> >>Thanx again,
    > >> >>Cary
    > >> >>
    > >> >>
    > >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
    > >> >>>I also added a comma after s.datein by mistake.
    > >> >>>I think that I need more coffee.
    > >> >>>
    > >> >>>
    > >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    > >> >>>news:3bab6af0@news.devx.com...
    > >> >>>> try this:
    > >> >>>>
    > >> >>>> select
    > >> >>>> e.serialnum,
    > >> >>>> e.[name]
    > >> >>>> s.status,
    > >> >>>> s.racfid,
    > >> >>>> s.dateout,
    > >> >>>> s.datein,
    > >> >>>> from equipmnt
    > >> >>>> right join (select
    > >> >>>> max(dateout) dateout,
    > >> >>>> serialnum,
    > >> >>>> status,
    > >> >>>> racfid,
    > >> >>>> datein
    > >> >>>> from signout
    > >> >>>> group by serialnum,racfid,
    > >> >>>> status, datein ) s on s.serialnum = e.serialnum
    > >> >>>> order by dateout desc
    > >> >>>>
    > >> >>>> you may have to change the group by in the derived table to get

    the
    > >> data
    > >> >>>the
    > >> >>>> way you want it.
    > >> >>>>
    > >> >>>> HTH
    > >> >>>>
    > >> >>>> Daniel Reber
    > >> >>>> Datamasters, Inc
    > >> >>>>
    > >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    > >> >>>> news:3bab6314$1@news.devx.com...
    > >> >>>> >
    > >> >>>> > I've noticed there's been a lot of questions on the select

    distinct
    > >> >>>> statement,
    > >> >>>> > and in many tries, I can't figure out how to use this correctly

    > in
    > >> >my
    > >> >>>> scenario.
    > >> >>>> >
    > >> >>>> > I currently have 2 DBs - one that lists products and one that

    lists
    > >> >>all
    > >> >>>> items
    > >> >>>> > that have been signed out (or in) at any time. This is for

    > >inventory
    > >> >>>> purposes.
    > >> >>>> > I am trying to create an HTML table that lists each of the

    items
    > >> only
    > >> >>>> once
    > >> >>>> > with the last time they've been signed out/in or blank if

    they've
    > >> never
    > >> >>>> been.
    > >> >>>> >
    > >> >>>> > My code currently is
    > >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME

    SIGNOUT.STATUS,
    > >> >>>> SIGNOUT.RACFID,
    > >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    > >> >>>> WEBSRV.EQUIPMNT
    > >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    > >> >>>> >
    > >> >>>> > This code still lists items that have been signed out/in more

    than
    > >> >once,
    > >> >>>> > multiple times. I've tried using sub-queries, but I'm not quite

    > >sure
    > >> >>of
    > >> >>>> > the exact syntax to make this work correctly.
    > >> >>>> >
    > >> >>>> > Hopefully this is enough information. Thanks to anyone who can

    > >help!
    > >> >>>> > Cary
    > >> >>>> >
    > >> >>>>
    > >> >>>>
    > >> >>>
    > >> >>>
    > >> >>
    > >> >
    > >>

    > >
    > >

    >




  12. #12
    Cary R Guest

    Re: Select Distinct


    You're really a great guy for helping.

    I changed the ID to include time which fixed that matter, however, I'm somewhat
    confused by your suggestion. Is S2 a second database I should create??


    "Daniel Reber" <dreber@dminconline.com> wrote:
    >you might want to consider changing that to an auto number. I would make
    >things easier.
    >the reason why I asked is so we can change the query to
    >
    >select
    > e.serialnum,
    > e.[name],
    > s2.status,
    > s2.racfid,
    > s1.dateout,
    > s2.datein
    > from equipmnt
    > right join (select
    > max(dateout) dateout, uniqueID
    > from signout
    > group by uniqueID) s1 s on s1.serialnum = e.serialnum
    > right join (select
    > uniqueID,
    > serialnum,
    > status,
    > racfid,
    > datein
    > from signout ) s2 on s2.uniqueID= s1.uniqueID
    > order by s1.dateout desc
    >
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3bab8a53$1@news.devx.com...
    >>
    >> Oh...sorry about that.
    >> It's actually a signoutID which is formed by concatenating the serial

    >number,
    >> username, and the signoutdate. (That actually brings up a flaw in that

    if
    >> someone signs an object in and out on the same day, there will be some

    >problems,
    >> so thank your for bringing that up.)
    >>
    >> But assuming that doesn't happen, will that help at all??
    >>
    >>
    >>
    >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >how can that be the unique id for signout if the serialnum is there

    >multiple
    >> >times?
    >> >
    >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >news:3bab884b$1@news.devx.com...
    >> >>
    >> >> Serialnum for both
    >> >>
    >> >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >> >
    >> >> >
    >> >> >>
    >> >> >>Thanx a lot for the quick response, however I'm still facing the

    same
    >> >problems.
    >> >> >>
    >> >> >>
    >> >> >>My main problem still exists in the duplication of data. For

    >example,
    >> >> say
    >> >> >>there are items A, B, C, D within the equipment database. Now, item

    >> A
    >> >and
    >> >> >>B have both been signed out, they appear fine. However, say C has

    >been
    >> >> >signed
    >> >> >>out, signed in, then signed back out again, all 3 occurences of the

    >> C
    >> >item
    >> >> >>signout are visible on the page. (By changing Right Join to Full

    >outer
    >> >> >join,
    >> >> >>I was able to see the D item with the table left blank as desired

    -
    >> I
    >> >don't
    >> >> >>know why the right join didn't work this time.)
    >> >> >>
    >> >> >>My goal is to somehow use the distinct command to only show the last
    >> >occurence
    >> >> >>of each item. It is very likely that your code was really close,
    >> >however
    >> >> >>I've been workin on this one line for a while now, and I seem to

    >always
    >> >> >end up with the same result.
    >> >> >
    >> >> >Maybe, to make it more simple...If I were just to type: select

    >distinct
    >> >> e.serialnum,
    >> >> >then it shows up correctly (obviously without other data), however

    >then
    >> >> I
    >> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so

    on,
    >> the
    >> >> >distinct command no longer functions as I want it to.
    >> >> >>
    >> >> >>Thanx again,
    >> >> >>Cary
    >> >> >>
    >> >> >>
    >> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >>>I also added a comma after s.datein by mistake.
    >> >> >>>I think that I need more coffee.
    >> >> >>>
    >> >> >>>
    >> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    >> >> >>>news:3bab6af0@news.devx.com...
    >> >> >>>> try this:
    >> >> >>>>
    >> >> >>>> select
    >> >> >>>> e.serialnum,
    >> >> >>>> e.[name]
    >> >> >>>> s.status,
    >> >> >>>> s.racfid,
    >> >> >>>> s.dateout,
    >> >> >>>> s.datein,
    >> >> >>>> from equipmnt
    >> >> >>>> right join (select
    >> >> >>>> max(dateout) dateout,
    >> >> >>>> serialnum,
    >> >> >>>> status,
    >> >> >>>> racfid,
    >> >> >>>> datein
    >> >> >>>> from signout
    >> >> >>>> group by serialnum,racfid,
    >> >> >>>> status, datein ) s on s.serialnum = e.serialnum
    >> >> >>>> order by dateout desc
    >> >> >>>>
    >> >> >>>> you may have to change the group by in the derived table to get

    >the
    >> >> data
    >> >> >>>the
    >> >> >>>> way you want it.
    >> >> >>>>
    >> >> >>>> HTH
    >> >> >>>>
    >> >> >>>> Daniel Reber
    >> >> >>>> Datamasters, Inc
    >> >> >>>>
    >> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >>>> news:3bab6314$1@news.devx.com...
    >> >> >>>> >
    >> >> >>>> > I've noticed there's been a lot of questions on the select

    >distinct
    >> >> >>>> statement,
    >> >> >>>> > and in many tries, I can't figure out how to use this correctly

    >> in
    >> >> >my
    >> >> >>>> scenario.
    >> >> >>>> >
    >> >> >>>> > I currently have 2 DBs - one that lists products and one that

    >lists
    >> >> >>all
    >> >> >>>> items
    >> >> >>>> > that have been signed out (or in) at any time. This is for
    >> >inventory
    >> >> >>>> purposes.
    >> >> >>>> > I am trying to create an HTML table that lists each of the

    >items
    >> >> only
    >> >> >>>> once
    >> >> >>>> > with the last time they've been signed out/in or blank if

    >they've
    >> >> never
    >> >> >>>> been.
    >> >> >>>> >
    >> >> >>>> > My code currently is
    >> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME

    >SIGNOUT.STATUS,
    >> >> >>>> SIGNOUT.RACFID,
    >> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
    >> >> >>>> WEBSRV.EQUIPMNT
    >> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    >> >> >>>> >
    >> >> >>>> > This code still lists items that have been signed out/in more

    >than
    >> >> >once,
    >> >> >>>> > multiple times. I've tried using sub-queries, but I'm not quite
    >> >sure
    >> >> >>of
    >> >> >>>> > the exact syntax to make this work correctly.
    >> >> >>>> >
    >> >> >>>> > Hopefully this is enough information. Thanks to anyone who

    can
    >> >help!
    >> >> >>>> > Cary
    >> >> >>>> >
    >> >> >>>>
    >> >> >>>>
    >> >> >>>
    >> >> >>>
    >> >> >>
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  13. #13
    Daniel Reber Guest

    Re: Select Distinct

    since you are trying to get distinct rows out of data that isn't distinct
    you will first need to pull the max(dateout) out of the first derived table
    then join it to the rest of the information in the second derived table.
    You will not need to make any other changes, derived tables are virtual
    tables.


    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3bab9020$1@news.devx.com...
    >
    > You're really a great guy for helping.
    >
    > I changed the ID to include time which fixed that matter, however, I'm

    somewhat
    > confused by your suggestion. Is S2 a second database I should create??
    >
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote:
    > >you might want to consider changing that to an auto number. I would make
    > >things easier.
    > >the reason why I asked is so we can change the query to
    > >
    > >select
    > > e.serialnum,
    > > e.[name],
    > > s2.status,
    > > s2.racfid,
    > > s1.dateout,
    > > s2.datein
    > > from equipmnt
    > > right join (select
    > > max(dateout) dateout, uniqueID
    > > from signout
    > > group by uniqueID) s1 s on s1.serialnum = e.serialnum
    > > right join (select
    > > uniqueID,
    > > serialnum,
    > > status,
    > > racfid,
    > > datein
    > > from signout ) s2 on s2.uniqueID= s1.uniqueID
    > > order by s1.dateout desc
    > >
    > >
    > >"Cary R" <cary.rotman@intria.com> wrote in message
    > >news:3bab8a53$1@news.devx.com...
    > >>
    > >> Oh...sorry about that.
    > >> It's actually a signoutID which is formed by concatenating the serial

    > >number,
    > >> username, and the signoutdate. (That actually brings up a flaw in that

    > if
    > >> someone signs an object in and out on the same day, there will be some

    > >problems,
    > >> so thank your for bringing that up.)
    > >>
    > >> But assuming that doesn't happen, will that help at all??
    > >>
    > >>
    > >>
    > >> "Daniel Reber" <dreber@dminconline.com> wrote:
    > >> >how can that be the unique id for signout if the serialnum is there

    > >multiple
    > >> >times?
    > >> >
    > >> >"Cary R" <cary.rotman@intria.com> wrote in message
    > >> >news:3bab884b$1@news.devx.com...
    > >> >>
    > >> >> Serialnum for both
    > >> >>
    > >> >> "Cary R" <cary.rotman@intria.com> wrote:
    > >> >> >
    > >> >> >
    > >> >> >>
    > >> >> >>Thanx a lot for the quick response, however I'm still facing the

    > same
    > >> >problems.
    > >> >> >>
    > >> >> >>
    > >> >> >>My main problem still exists in the duplication of data. For

    > >example,
    > >> >> say
    > >> >> >>there are items A, B, C, D within the equipment database. Now,

    item
    > >> A
    > >> >and
    > >> >> >>B have both been signed out, they appear fine. However, say C has

    > >been
    > >> >> >signed
    > >> >> >>out, signed in, then signed back out again, all 3 occurences of

    the
    > >> C
    > >> >item
    > >> >> >>signout are visible on the page. (By changing Right Join to Full

    > >outer
    > >> >> >join,
    > >> >> >>I was able to see the D item with the table left blank as desired

    > -
    > >> I
    > >> >don't
    > >> >> >>know why the right join didn't work this time.)
    > >> >> >>
    > >> >> >>My goal is to somehow use the distinct command to only show the

    last
    > >> >occurence
    > >> >> >>of each item. It is very likely that your code was really close,
    > >> >however
    > >> >> >>I've been workin on this one line for a while now, and I seem to

    > >always
    > >> >> >end up with the same result.
    > >> >> >
    > >> >> >Maybe, to make it more simple...If I were just to type: select

    > >distinct
    > >> >> e.serialnum,
    > >> >> >then it shows up correctly (obviously without other data), however

    > >then
    > >> >> I
    > >> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so

    > on,
    > >> the
    > >> >> >distinct command no longer functions as I want it to.
    > >> >> >>
    > >> >> >>Thanx again,
    > >> >> >>Cary
    > >> >> >>
    > >> >> >>
    > >> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
    > >> >> >>>I also added a comma after s.datein by mistake.
    > >> >> >>>I think that I need more coffee.
    > >> >> >>>
    > >> >> >>>
    > >> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    > >> >> >>>news:3bab6af0@news.devx.com...
    > >> >> >>>> try this:
    > >> >> >>>>
    > >> >> >>>> select
    > >> >> >>>> e.serialnum,
    > >> >> >>>> e.[name]
    > >> >> >>>> s.status,
    > >> >> >>>> s.racfid,
    > >> >> >>>> s.dateout,
    > >> >> >>>> s.datein,
    > >> >> >>>> from equipmnt
    > >> >> >>>> right join (select
    > >> >> >>>> max(dateout) dateout,
    > >> >> >>>> serialnum,
    > >> >> >>>> status,
    > >> >> >>>> racfid,
    > >> >> >>>> datein
    > >> >> >>>> from signout
    > >> >> >>>> group by serialnum,racfid,
    > >> >> >>>> status, datein ) s on s.serialnum = e.serialnum
    > >> >> >>>> order by dateout desc
    > >> >> >>>>
    > >> >> >>>> you may have to change the group by in the derived table to get

    > >the
    > >> >> data
    > >> >> >>>the
    > >> >> >>>> way you want it.
    > >> >> >>>>
    > >> >> >>>> HTH
    > >> >> >>>>
    > >> >> >>>> Daniel Reber
    > >> >> >>>> Datamasters, Inc
    > >> >> >>>>
    > >> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    > >> >> >>>> news:3bab6314$1@news.devx.com...
    > >> >> >>>> >
    > >> >> >>>> > I've noticed there's been a lot of questions on the select

    > >distinct
    > >> >> >>>> statement,
    > >> >> >>>> > and in many tries, I can't figure out how to use this

    correctly
    > >> in
    > >> >> >my
    > >> >> >>>> scenario.
    > >> >> >>>> >
    > >> >> >>>> > I currently have 2 DBs - one that lists products and one that

    > >lists
    > >> >> >>all
    > >> >> >>>> items
    > >> >> >>>> > that have been signed out (or in) at any time. This is for
    > >> >inventory
    > >> >> >>>> purposes.
    > >> >> >>>> > I am trying to create an HTML table that lists each of the

    > >items
    > >> >> only
    > >> >> >>>> once
    > >> >> >>>> > with the last time they've been signed out/in or blank if

    > >they've
    > >> >> never
    > >> >> >>>> been.
    > >> >> >>>> >
    > >> >> >>>> > My code currently is
    > >> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME

    > >SIGNOUT.STATUS,
    > >> >> >>>> SIGNOUT.RACFID,
    > >> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT

    JOIN
    > >> >> >>>> WEBSRV.EQUIPMNT
    > >> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    > >> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    > >> >> >>>> >
    > >> >> >>>> > This code still lists items that have been signed out/in more

    > >than
    > >> >> >once,
    > >> >> >>>> > multiple times. I've tried using sub-queries, but I'm not

    quite
    > >> >sure
    > >> >> >>of
    > >> >> >>>> > the exact syntax to make this work correctly.
    > >> >> >>>> >
    > >> >> >>>> > Hopefully this is enough information. Thanks to anyone who

    > can
    > >> >help!
    > >> >> >>>> > Cary
    > >> >> >>>> >
    > >> >> >>>>
    > >> >> >>>>
    > >> >> >>>
    > >> >> >>>
    > >> >> >>
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  14. #14
    Cary R Guest

    Re: Select Distinct


    Perfect...thanx a lot for everything!

    Cary

    "Daniel Reber" <dreber@dminconline.com> wrote:
    >since you are trying to get distinct rows out of data that isn't distinct
    >you will first need to pull the max(dateout) out of the first derived table
    >then join it to the rest of the information in the second derived table.
    >You will not need to make any other changes, derived tables are virtual
    >tables.
    >
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3bab9020$1@news.devx.com...
    >>
    >> You're really a great guy for helping.
    >>
    >> I changed the ID to include time which fixed that matter, however, I'm

    >somewhat
    >> confused by your suggestion. Is S2 a second database I should create??
    >>
    >>
    >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >you might want to consider changing that to an auto number. I would

    make
    >> >things easier.
    >> >the reason why I asked is so we can change the query to
    >> >
    >> >select
    >> > e.serialnum,
    >> > e.[name],
    >> > s2.status,
    >> > s2.racfid,
    >> > s1.dateout,
    >> > s2.datein
    >> > from equipmnt
    >> > right join (select
    >> > max(dateout) dateout, uniqueID
    >> > from signout
    >> > group by uniqueID) s1 s on s1.serialnum = e.serialnum
    >> > right join (select
    >> > uniqueID,
    >> > serialnum,
    >> > status,
    >> > racfid,
    >> > datein
    >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
    >> > order by s1.dateout desc
    >> >
    >> >
    >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >news:3bab8a53$1@news.devx.com...
    >> >>
    >> >> Oh...sorry about that.
    >> >> It's actually a signoutID which is formed by concatenating the serial
    >> >number,
    >> >> username, and the signoutdate. (That actually brings up a flaw in

    that
    >> if
    >> >> someone signs an object in and out on the same day, there will be some
    >> >problems,
    >> >> so thank your for bringing that up.)
    >> >>
    >> >> But assuming that doesn't happen, will that help at all??
    >> >>
    >> >>
    >> >>
    >> >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >how can that be the unique id for signout if the serialnum is there
    >> >multiple
    >> >> >times?
    >> >> >
    >> >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >news:3bab884b$1@news.devx.com...
    >> >> >>
    >> >> >> Serialnum for both
    >> >> >>
    >> >> >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >> >> >
    >> >> >> >
    >> >> >> >>
    >> >> >> >>Thanx a lot for the quick response, however I'm still facing the

    >> same
    >> >> >problems.
    >> >> >> >>
    >> >> >> >>
    >> >> >> >>My main problem still exists in the duplication of data. For
    >> >example,
    >> >> >> say
    >> >> >> >>there are items A, B, C, D within the equipment database. Now,

    >item
    >> >> A
    >> >> >and
    >> >> >> >>B have both been signed out, they appear fine. However, say C

    has
    >> >been
    >> >> >> >signed
    >> >> >> >>out, signed in, then signed back out again, all 3 occurences of

    >the
    >> >> C
    >> >> >item
    >> >> >> >>signout are visible on the page. (By changing Right Join to Full
    >> >outer
    >> >> >> >join,
    >> >> >> >>I was able to see the D item with the table left blank as desired

    >> -
    >> >> I
    >> >> >don't
    >> >> >> >>know why the right join didn't work this time.)
    >> >> >> >>
    >> >> >> >>My goal is to somehow use the distinct command to only show the

    >last
    >> >> >occurence
    >> >> >> >>of each item. It is very likely that your code was really close,
    >> >> >however
    >> >> >> >>I've been workin on this one line for a while now, and I seem

    to
    >> >always
    >> >> >> >end up with the same result.
    >> >> >> >
    >> >> >> >Maybe, to make it more simple...If I were just to type: select
    >> >distinct
    >> >> >> e.serialnum,
    >> >> >> >then it shows up correctly (obviously without other data), however
    >> >then
    >> >> >> I
    >> >> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and

    so
    >> on,
    >> >> the
    >> >> >> >distinct command no longer functions as I want it to.
    >> >> >> >>
    >> >> >> >>Thanx again,
    >> >> >> >>Cary
    >> >> >> >>
    >> >> >> >>
    >> >> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >> >>>I also added a comma after s.datein by mistake.
    >> >> >> >>>I think that I need more coffee.
    >> >> >> >>>
    >> >> >> >>>
    >> >> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
    >> >> >> >>>news:3bab6af0@news.devx.com...
    >> >> >> >>>> try this:
    >> >> >> >>>>
    >> >> >> >>>> select
    >> >> >> >>>> e.serialnum,
    >> >> >> >>>> e.[name]
    >> >> >> >>>> s.status,
    >> >> >> >>>> s.racfid,
    >> >> >> >>>> s.dateout,
    >> >> >> >>>> s.datein,
    >> >> >> >>>> from equipmnt
    >> >> >> >>>> right join (select
    >> >> >> >>>> max(dateout) dateout,
    >> >> >> >>>> serialnum,
    >> >> >> >>>> status,
    >> >> >> >>>> racfid,
    >> >> >> >>>> datein
    >> >> >> >>>> from signout
    >> >> >> >>>> group by serialnum,racfid,
    >> >> >> >>>> status, datein ) s on s.serialnum = e.serialnum
    >> >> >> >>>> order by dateout desc
    >> >> >> >>>>
    >> >> >> >>>> you may have to change the group by in the derived table to

    get
    >> >the
    >> >> >> data
    >> >> >> >>>the
    >> >> >> >>>> way you want it.
    >> >> >> >>>>
    >> >> >> >>>> HTH
    >> >> >> >>>>
    >> >> >> >>>> Daniel Reber
    >> >> >> >>>> Datamasters, Inc
    >> >> >> >>>>
    >> >> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >> >>>> news:3bab6314$1@news.devx.com...
    >> >> >> >>>> >
    >> >> >> >>>> > I've noticed there's been a lot of questions on the select
    >> >distinct
    >> >> >> >>>> statement,
    >> >> >> >>>> > and in many tries, I can't figure out how to use this

    >correctly
    >> >> in
    >> >> >> >my
    >> >> >> >>>> scenario.
    >> >> >> >>>> >
    >> >> >> >>>> > I currently have 2 DBs - one that lists products and one

    that
    >> >lists
    >> >> >> >>all
    >> >> >> >>>> items
    >> >> >> >>>> > that have been signed out (or in) at any time. This is for
    >> >> >inventory
    >> >> >> >>>> purposes.
    >> >> >> >>>> > I am trying to create an HTML table that lists each of the
    >> >items
    >> >> >> only
    >> >> >> >>>> once
    >> >> >> >>>> > with the last time they've been signed out/in or blank if
    >> >they've
    >> >> >> never
    >> >> >> >>>> been.
    >> >> >> >>>> >
    >> >> >> >>>> > My code currently is
    >> >> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME
    >> >SIGNOUT.STATUS,
    >> >> >> >>>> SIGNOUT.RACFID,
    >> >> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT

    >JOIN
    >> >> >> >>>> WEBSRV.EQUIPMNT
    >> >> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >> >> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
    >> >> >> >>>> >
    >> >> >> >>>> > This code still lists items that have been signed out/in

    more
    >> >than
    >> >> >> >once,
    >> >> >> >>>> > multiple times. I've tried using sub-queries, but I'm not

    >quite
    >> >> >sure
    >> >> >> >>of
    >> >> >> >>>> > the exact syntax to make this work correctly.
    >> >> >> >>>> >
    >> >> >> >>>> > Hopefully this is enough information. Thanks to anyone who

    >> can
    >> >> >help!
    >> >> >> >>>> > Cary
    >> >> >> >>>> >
    >> >> >> >>>>
    >> >> >> >>>>
    >> >> >> >>>
    >> >> >> >>>
    >> >> >> >>
    >> >> >> >
    >> >> >>
    >> >> >
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  15. #15
    frye3k Guest

    Re: Select Distinct


    from what i can see, the code snippet from the other guy is good, however
    i feel like you should do a LEFT OUTER JOIN rather than a RIGHT, or a FULL...
    let me know how it turns out...

    Thanks,
    Garland


    "Cary R" <cary.rotman@intria.com> wrote:
    >
    >I've noticed there's been a lot of questions on the select distinct statement,
    >and in many tries, I can't figure out how to use this correctly in my scenario.
    >
    >I currently have 2 DBs - one that lists products and one that lists all

    items
    >that have been signed out (or in) at any time. This is for inventory purposes.
    > I am trying to create an HTML table that lists each of the items only once
    >with the last time they've been signed out/in or blank if they've never

    been.
    >
    >My code currently is
    > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS, SIGNOUT.RACFID,
    >SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN WEBSRV.EQUIPMNT
    >ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
    >ORDER BY SIGNOUT.DATEOUT DESC
    >
    >This code still lists items that have been signed out/in more than once,
    >multiple times. I've tried using sub-queries, but I'm not quite sure of
    >the exact syntax to make this work correctly.
    >
    >Hopefully this is enough information. Thanks to anyone who can help!
    >Cary
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center