Select Distinct - Page 2


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25

Thread: Select Distinct

  1. #16
    Daniel Reber Guest

    Re: Select Distinct

    Cary will still get duplicate rows that way.

    "frye3k" <frye3k@hotmail.com> wrote in message
    news:3bad4a88$1@news.devx.com...
    >
    > 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
    > >

    >




  2. #17
    Daniel Reber Guest

    Re: Select Distinct

    I think that I forgot my right from my left. You are correct, a left join
    should have been used with my sample sql.


    "frye3k" <frye3k@hotmail.com> wrote in message
    news:3bad4a88$1@news.devx.com...
    >
    > 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
    > >

    >




  3. #18
    Cary R Guest

    Re: Select Distinct


    Sorry to bother you guys again, however I'm still having a few problems.

    The first error is due to the 's' after the first right join statement...so
    I took that out.

    Then it states that I don't have s1.serialnum, yet when I put it in, it gives
    me a 'no group by clause' error.

    Also, there is an error on the e.serialnum line.

    Obviously, you can tell that I'm, by no means, on expert with SQL, so even
    by playing around, I can't get the final product.

    Thanx again, Cary


    "Cary R" <cary.rotman@intria.com> wrote:
    >
    >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
    >>> >> >> >>>> >
    >>> >> >> >>>>
    >>> >> >> >>>>
    >>> >> >> >>>
    >>> >> >> >>>
    >>> >> >> >>
    >>> >> >> >
    >>> >> >>
    >>> >> >
    >>> >> >
    >>> >>
    >>> >
    >>> >
    >>>

    >>
    >>

    >



  4. #19
    Daniel Reber Guest

    Re: Select Distinct

    I guess that I should check my work better.
    try this:

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

    I changed the right joins to lefts joins like frye3k suggested.


    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3baf56c8@news.devx.com...
    >
    > Sorry to bother you guys again, however I'm still having a few problems.
    >
    > The first error is due to the 's' after the first right join

    statement...so
    > I took that out.
    >
    > Then it states that I don't have s1.serialnum, yet when I put it in, it

    gives
    > me a 'no group by clause' error.
    >
    > Also, there is an error on the e.serialnum line.
    >
    > Obviously, you can tell that I'm, by no means, on expert with SQL, so even
    > by playing around, I can't get the final product.
    >
    > Thanx again, Cary
    >
    >
    > "Cary R" <cary.rotman@intria.com> wrote:
    > >
    > >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
    > >>> >> >> >>>> >
    > >>> >> >> >>>>
    > >>> >> >> >>>>
    > >>> >> >> >>>
    > >>> >> >> >>>
    > >>> >> >> >>
    > >>> >> >> >
    > >>> >> >>
    > >>> >> >
    > >>> >> >
    > >>> >>
    > >>> >
    > >>> >
    > >>>
    > >>
    > >>

    > >

    >




  5. #20
    Cary R Guest

    Re: Select Distinct


    Unfortunately, it didn't work again. When I tried using the e.xxxx, there
    was an error that e.xxxx is not a column, etc. I tried changing it to equipmnt.xxxx,
    which then let the program run, however, the duplicates were still there!
    It seems like nothing is working!!

    I think I'll rethink the way I attempt this problem, unless there's another
    great idea.

    Thanx,
    Cary


    "Daniel Reber" <dreber@dminconline.com> wrote:
    >I guess that I should check my work better.
    >try this:
    >
    >select
    > e.serialnum,
    > e.[name],
    > s2.status,
    > s2.racfid,
    > s1.dateout,
    > s2.datein
    > from equipmnt
    > left join (select
    > max(dateout) dateout, uniqueID, serialnum
    > from signout
    > group by uniqueID,serialnum) s1 on s1.serialnum = e.serialnum
    > left join (select
    > uniqueID,
    > serialnum,
    > status,
    > racfid,
    > datein
    > from signout ) s2 on s2.uniqueID= s1.uniqueID
    > order by s1.dateout desc
    >
    >I changed the right joins to lefts joins like frye3k suggested.
    >
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3baf56c8@news.devx.com...
    >>
    >> Sorry to bother you guys again, however I'm still having a few problems.
    >>
    >> The first error is due to the 's' after the first right join

    >statement...so
    >> I took that out.
    >>
    >> Then it states that I don't have s1.serialnum, yet when I put it in, it

    >gives
    >> me a 'no group by clause' error.
    >>
    >> Also, there is an error on the e.serialnum line.
    >>
    >> Obviously, you can tell that I'm, by no means, on expert with SQL, so

    even
    >> by playing around, I can't get the final product.
    >>
    >> Thanx again, Cary
    >>
    >>
    >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >
    >> >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
    >> >>> >> >> >>>> >
    >> >>> >> >> >>>>
    >> >>> >> >> >>>>
    >> >>> >> >> >>>
    >> >>> >> >> >>>
    >> >>> >> >> >>
    >> >>> >> >> >
    >> >>> >> >>
    >> >>> >> >
    >> >>> >> >
    >> >>> >>
    >> >>> >
    >> >>> >
    >> >>>
    >> >>
    >> >>
    >> >

    >>

    >
    >



  6. #21
    Daniel Reber Guest

    Re: Select Distinct

    could you post the sql that you are using. thanks
    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3baf6acd$1@news.devx.com...
    >
    > Unfortunately, it didn't work again. When I tried using the e.xxxx, there
    > was an error that e.xxxx is not a column, etc. I tried changing it to

    equipmnt.xxxx,
    > which then let the program run, however, the duplicates were still there!
    > It seems like nothing is working!!
    >
    > I think I'll rethink the way I attempt this problem, unless there's

    another
    > great idea.
    >
    > Thanx,
    > Cary
    >
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote:
    > >I guess that I should check my work better.
    > >try this:
    > >
    > >select
    > > e.serialnum,
    > > e.[name],
    > > s2.status,
    > > s2.racfid,
    > > s1.dateout,
    > > s2.datein
    > > from equipmnt
    > > left join (select
    > > max(dateout) dateout, uniqueID, serialnum
    > > from signout
    > > group by uniqueID,serialnum) s1 on s1.serialnum =

    e.serialnum
    > > left join (select
    > > uniqueID,
    > > serialnum,
    > > status,
    > > racfid,
    > > datein
    > > from signout ) s2 on s2.uniqueID= s1.uniqueID
    > > order by s1.dateout desc
    > >
    > >I changed the right joins to lefts joins like frye3k suggested.
    > >
    > >
    > >"Cary R" <cary.rotman@intria.com> wrote in message
    > >news:3baf56c8@news.devx.com...
    > >>
    > >> Sorry to bother you guys again, however I'm still having a few

    problems.
    > >>
    > >> The first error is due to the 's' after the first right join

    > >statement...so
    > >> I took that out.
    > >>
    > >> Then it states that I don't have s1.serialnum, yet when I put it in, it

    > >gives
    > >> me a 'no group by clause' error.
    > >>
    > >> Also, there is an error on the e.serialnum line.
    > >>
    > >> Obviously, you can tell that I'm, by no means, on expert with SQL, so

    > even
    > >> by playing around, I can't get the final product.
    > >>
    > >> Thanx again, Cary
    > >>
    > >>
    > >> "Cary R" <cary.rotman@intria.com> wrote:
    > >> >
    > >> >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
    > >> >>> >> >> >>>> >
    > >> >>> >> >> >>>>
    > >> >>> >> >> >>>>
    > >> >>> >> >> >>>
    > >> >>> >> >> >>>
    > >> >>> >> >> >>
    > >> >>> >> >> >
    > >> >>> >> >>
    > >> >>> >> >
    > >> >>> >> >
    > >> >>> >>
    > >> >>> >
    > >> >>> >
    > >> >>>
    > >> >>
    > >> >>
    > >> >
    > >>

    > >
    > >

    >




  7. #22
    Cary R Guest

    Re: Select Distinct


    select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid, s1.dateout,
    s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
    serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
    = equipmnt.serialnum left join (select signoutID, serialnum, status, racfid,
    datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
    desc

    I'm using Net.Data, a program that combines HTML, SQL, and other languages
    to produce the website. Perhaps, this is the reason that my data isn't displayed
    as desired. I don't know??

    Cary


    "Daniel Reber" <dreber@dminconline.com> wrote:
    >could you post the sql that you are using. thanks
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3baf6acd$1@news.devx.com...
    >>
    >> Unfortunately, it didn't work again. When I tried using the e.xxxx, there
    >> was an error that e.xxxx is not a column, etc. I tried changing it to

    >equipmnt.xxxx,
    >> which then let the program run, however, the duplicates were still there!
    >> It seems like nothing is working!!
    >>
    >> I think I'll rethink the way I attempt this problem, unless there's

    >another
    >> great idea.
    >>
    >> Thanx,
    >> Cary
    >>
    >>
    >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >I guess that I should check my work better.
    >> >try this:
    >> >
    >> >select
    >> > e.serialnum,
    >> > e.[name],
    >> > s2.status,
    >> > s2.racfid,
    >> > s1.dateout,
    >> > s2.datein
    >> > from equipmnt
    >> > left join (select
    >> > max(dateout) dateout, uniqueID, serialnum
    >> > from signout
    >> > group by uniqueID,serialnum) s1 on s1.serialnum =

    >e.serialnum
    >> > left join (select
    >> > uniqueID,
    >> > serialnum,
    >> > status,
    >> > racfid,
    >> > datein
    >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
    >> > order by s1.dateout desc
    >> >
    >> >I changed the right joins to lefts joins like frye3k suggested.
    >> >
    >> >
    >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >news:3baf56c8@news.devx.com...
    >> >>
    >> >> Sorry to bother you guys again, however I'm still having a few

    >problems.
    >> >>
    >> >> The first error is due to the 's' after the first right join
    >> >statement...so
    >> >> I took that out.
    >> >>
    >> >> Then it states that I don't have s1.serialnum, yet when I put it in,

    it
    >> >gives
    >> >> me a 'no group by clause' error.
    >> >>
    >> >> Also, there is an error on the e.serialnum line.
    >> >>
    >> >> Obviously, you can tell that I'm, by no means, on expert with SQL,

    so
    >> even
    >> >> by playing around, I can't get the final product.
    >> >>
    >> >> Thanx again, Cary
    >> >>
    >> >>
    >> >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >> >
    >> >> >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
    >> >> >>> >> >> >>>> >
    >> >> >>> >> >> >>>>
    >> >> >>> >> >> >>>>
    >> >> >>> >> >> >>>
    >> >> >>> >> >> >>>
    >> >> >>> >> >> >>
    >> >> >>> >> >> >
    >> >> >>> >> >>
    >> >> >>> >> >
    >> >> >>> >> >
    >> >> >>> >>
    >> >> >>> >
    >> >> >>> >
    >> >> >>>
    >> >> >>
    >> >> >>
    >> >> >
    >> >>
    >> >
    >> >

    >>

    >
    >



  8. #23
    Daniel Reber Guest

    Re: Select Distinct

    It seems that it may be data related.

    on the s1 join you are only bringing back one row for each row in equipmnt.
    could you run these scripts to test the values.

    select count(*) from equipmnt
    select distinct serialnum from equipmnt

    the values should be the same

    "Cary R" <cary.rotman@intria.com> wrote in message
    news:3baf6dd8$1@news.devx.com...
    >
    > select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,

    s1.dateout,
    > s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
    > serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
    > = equipmnt.serialnum left join (select signoutID, serialnum, status,

    racfid,
    > datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
    > desc
    >
    > I'm using Net.Data, a program that combines HTML, SQL, and other languages
    > to produce the website. Perhaps, this is the reason that my data isn't

    displayed
    > as desired. I don't know??
    >
    > Cary
    >
    >
    > "Daniel Reber" <dreber@dminconline.com> wrote:
    > >could you post the sql that you are using. thanks
    > >"Cary R" <cary.rotman@intria.com> wrote in message
    > >news:3baf6acd$1@news.devx.com...
    > >>
    > >> Unfortunately, it didn't work again. When I tried using the e.xxxx,

    there
    > >> was an error that e.xxxx is not a column, etc. I tried changing it to

    > >equipmnt.xxxx,
    > >> which then let the program run, however, the duplicates were still

    there!
    > >> It seems like nothing is working!!
    > >>
    > >> I think I'll rethink the way I attempt this problem, unless there's

    > >another
    > >> great idea.
    > >>
    > >> Thanx,
    > >> Cary
    > >>
    > >>
    > >> "Daniel Reber" <dreber@dminconline.com> wrote:
    > >> >I guess that I should check my work better.
    > >> >try this:
    > >> >
    > >> >select
    > >> > e.serialnum,
    > >> > e.[name],
    > >> > s2.status,
    > >> > s2.racfid,
    > >> > s1.dateout,
    > >> > s2.datein
    > >> > from equipmnt
    > >> > left join (select
    > >> > max(dateout) dateout, uniqueID, serialnum
    > >> > from signout
    > >> > group by uniqueID,serialnum) s1 on s1.serialnum =

    > >e.serialnum
    > >> > left join (select
    > >> > uniqueID,
    > >> > serialnum,
    > >> > status,
    > >> > racfid,
    > >> > datein
    > >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
    > >> > order by s1.dateout desc
    > >> >
    > >> >I changed the right joins to lefts joins like frye3k suggested.
    > >> >
    > >> >
    > >> >"Cary R" <cary.rotman@intria.com> wrote in message
    > >> >news:3baf56c8@news.devx.com...
    > >> >>
    > >> >> Sorry to bother you guys again, however I'm still having a few

    > >problems.
    > >> >>
    > >> >> The first error is due to the 's' after the first right join
    > >> >statement...so
    > >> >> I took that out.
    > >> >>
    > >> >> Then it states that I don't have s1.serialnum, yet when I put it in,

    > it
    > >> >gives
    > >> >> me a 'no group by clause' error.
    > >> >>
    > >> >> Also, there is an error on the e.serialnum line.
    > >> >>
    > >> >> Obviously, you can tell that I'm, by no means, on expert with SQL,

    > so
    > >> even
    > >> >> by playing around, I can't get the final product.
    > >> >>
    > >> >> Thanx again, Cary
    > >> >>
    > >> >>
    > >> >> "Cary R" <cary.rotman@intria.com> wrote:
    > >> >> >
    > >> >> >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
    > >> >> >>> >> >> >>>> >
    > >> >> >>> >> >> >>>>
    > >> >> >>> >> >> >>>>
    > >> >> >>> >> >> >>>
    > >> >> >>> >> >> >>>
    > >> >> >>> >> >> >>
    > >> >> >>> >> >> >
    > >> >> >>> >> >>
    > >> >> >>> >> >
    > >> >> >>> >> >
    > >> >> >>> >>
    > >> >> >>> >
    > >> >> >>> >
    > >> >> >>>
    > >> >> >>
    > >> >> >>
    > >> >> >
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




  9. #24
    Cary R Guest

    Re: Select Distinct


    On the select count - it displayed '4'
    The select distinct displayed the 4 unique serial numbers.

    The select distinct of the serial numbers in the signout displays 3 unique
    serial numbers, as desired, because 1 of the objects has never been signed
    out.

    When I display it on the webpage, there are 7 pieces of data shown, as in
    my testing I have signed in and/or out an object 6 times, while the 1 object
    has never been touched.

    All I want (which you probably know) is to display the last event of each
    piece of equipment or just the piece of equipment if its not in the signout
    database.

    I have access to a program which diplays both entire database, so I know
    that all the data is entered correctly.

    Cary



    "Daniel Reber" <dreber@dminconline.com> wrote:
    >It seems that it may be data related.
    >
    >on the s1 join you are only bringing back one row for each row in equipmnt.
    >could you run these scripts to test the values.
    >
    >select count(*) from equipmnt
    >select distinct serialnum from equipmnt
    >
    >the values should be the same
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3baf6dd8$1@news.devx.com...
    >>
    >> select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,

    >s1.dateout,
    >> s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
    >> serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
    >> = equipmnt.serialnum left join (select signoutID, serialnum, status,

    >racfid,
    >> datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
    >> desc
    >>
    >> I'm using Net.Data, a program that combines HTML, SQL, and other languages
    >> to produce the website. Perhaps, this is the reason that my data isn't

    >displayed
    >> as desired. I don't know??
    >>
    >> Cary
    >>
    >>
    >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >could you post the sql that you are using. thanks
    >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >news:3baf6acd$1@news.devx.com...
    >> >>
    >> >> Unfortunately, it didn't work again. When I tried using the e.xxxx,

    >there
    >> >> was an error that e.xxxx is not a column, etc. I tried changing it

    to
    >> >equipmnt.xxxx,
    >> >> which then let the program run, however, the duplicates were still

    >there!
    >> >> It seems like nothing is working!!
    >> >>
    >> >> I think I'll rethink the way I attempt this problem, unless there's
    >> >another
    >> >> great idea.
    >> >>
    >> >> Thanx,
    >> >> Cary
    >> >>
    >> >>
    >> >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >I guess that I should check my work better.
    >> >> >try this:
    >> >> >
    >> >> >select
    >> >> > e.serialnum,
    >> >> > e.[name],
    >> >> > s2.status,
    >> >> > s2.racfid,
    >> >> > s1.dateout,
    >> >> > s2.datein
    >> >> > from equipmnt
    >> >> > left join (select
    >> >> > max(dateout) dateout, uniqueID, serialnum
    >> >> > from signout
    >> >> > group by uniqueID,serialnum) s1 on s1.serialnum =
    >> >e.serialnum
    >> >> > left join (select
    >> >> > uniqueID,
    >> >> > serialnum,
    >> >> > status,
    >> >> > racfid,
    >> >> > datein
    >> >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
    >> >> > order by s1.dateout desc
    >> >> >
    >> >> >I changed the right joins to lefts joins like frye3k suggested.
    >> >> >
    >> >> >
    >> >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >news:3baf56c8@news.devx.com...
    >> >> >>
    >> >> >> Sorry to bother you guys again, however I'm still having a few
    >> >problems.
    >> >> >>
    >> >> >> The first error is due to the 's' after the first right join
    >> >> >statement...so
    >> >> >> I took that out.
    >> >> >>
    >> >> >> Then it states that I don't have s1.serialnum, yet when I put it

    in,
    >> it
    >> >> >gives
    >> >> >> me a 'no group by clause' error.
    >> >> >>
    >> >> >> Also, there is an error on the e.serialnum line.
    >> >> >>
    >> >> >> Obviously, you can tell that I'm, by no means, on expert with SQL,

    >> so
    >> >> even
    >> >> >> by playing around, I can't get the final product.
    >> >> >>
    >> >> >> Thanx again, Cary
    >> >> >>
    >> >> >>
    >> >> >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >> >> >
    >> >> >> >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?
    >> >> >> >>> >> >



  10. #25
    Cary R Guest

    Re: Select Distinct


    To be honest, I have no idea, however the IBM reference book I have says version
    4. I don't know if they're related though.

    I was thinking of using a sub query, yet I wasn't quite sure on how to structure
    it



    "Daniel Reber" <dreber@dminconline.com> wrote:
    >I see what is wrong. I don't think that you can do a simple select
    >statement to get the data that you want. What version of SQL Sever are

    you
    >using?
    >
    >
    >
    >"Cary R" <cary.rotman@intria.com> wrote in message
    >news:3baf751b$1@news.devx.com...
    >>
    >> On the select count - it displayed '4'
    >> The select distinct displayed the 4 unique serial numbers.
    >>
    >> The select distinct of the serial numbers in the signout displays 3 unique
    >> serial numbers, as desired, because 1 of the objects has never been signed
    >> out.
    >>
    >> When I display it on the webpage, there are 7 pieces of data shown, as

    in
    >> my testing I have signed in and/or out an object 6 times, while the 1

    >object
    >> has never been touched.
    >>
    >> All I want (which you probably know) is to display the last event of each
    >> piece of equipment or just the piece of equipment if its not in the

    >signout
    >> database.
    >>
    >> I have access to a program which diplays both entire database, so I know
    >> that all the data is entered correctly.
    >>
    >> Cary
    >>
    >>
    >>
    >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >It seems that it may be data related.
    >> >
    >> >on the s1 join you are only bringing back one row for each row in

    >equipmnt.
    >> >could you run these scripts to test the values.
    >> >
    >> >select count(*) from equipmnt
    >> >select distinct serialnum from equipmnt
    >> >
    >> >the values should be the same
    >> >
    >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >news:3baf6dd8$1@news.devx.com...
    >> >>
    >> >> select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,
    >> >s1.dateout,
    >> >> s2.datein from equipmnt left join (select max(dateout) dateout,

    >signoutID,
    >> >> serialnum from signout group by signoutID, serialnum) s1 on

    >s1.serialnum
    >> >> = equipmnt.serialnum left join (select signoutID, serialnum, status,
    >> >racfid,
    >> >> datein from signout ) s2 on s2.signoutID= s1.signoutID order by

    >s1.dateout
    >> >> desc
    >> >>
    >> >> I'm using Net.Data, a program that combines HTML, SQL, and other

    >languages
    >> >> to produce the website. Perhaps, this is the reason that my data isn't
    >> >displayed
    >> >> as desired. I don't know??
    >> >>
    >> >> Cary
    >> >>
    >> >>
    >> >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >could you post the sql that you are using. thanks
    >> >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >news:3baf6acd$1@news.devx.com...
    >> >> >>
    >> >> >> Unfortunately, it didn't work again. When I tried using the e.xxxx,
    >> >there
    >> >> >> was an error that e.xxxx is not a column, etc. I tried changing

    it
    >> to
    >> >> >equipmnt.xxxx,
    >> >> >> which then let the program run, however, the duplicates were still
    >> >there!
    >> >> >> It seems like nothing is working!!
    >> >> >>
    >> >> >> I think I'll rethink the way I attempt this problem, unless there's
    >> >> >another
    >> >> >> great idea.
    >> >> >>
    >> >> >> Thanx,
    >> >> >> Cary
    >> >> >>
    >> >> >>
    >> >> >> "Daniel Reber" <dreber@dminconline.com> wrote:
    >> >> >> >I guess that I should check my work better.
    >> >> >> >try this:
    >> >> >> >
    >> >> >> >select
    >> >> >> > e.serialnum,
    >> >> >> > e.[name],
    >> >> >> > s2.status,
    >> >> >> > s2.racfid,
    >> >> >> > s1.dateout,
    >> >> >> > s2.datein
    >> >> >> > from equipmnt
    >> >> >> > left join (select
    >> >> >> > max(dateout) dateout, uniqueID, serialnum
    >> >> >> > from signout
    >> >> >> > group by uniqueID,serialnum) s1 on s1.serialnum =
    >> >> >e.serialnum
    >> >> >> > left join (select
    >> >> >> > uniqueID,
    >> >> >> > serialnum,
    >> >> >> > status,
    >> >> >> > racfid,
    >> >> >> > datein
    >> >> >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
    >> >> >> > order by s1.dateout desc
    >> >> >> >
    >> >> >> >I changed the right joins to lefts joins like frye3k suggested.
    >> >> >> >
    >> >> >> >
    >> >> >> >"Cary R" <cary.rotman@intria.com> wrote in message
    >> >> >> >news:3baf56c8@news.devx.com...
    >> >> >> >>
    >> >> >> >> Sorry to bother you guys again, however I'm still having a few
    >> >> >problems.
    >> >> >> >>
    >> >> >> >> The first error is due to the 's' after the first right join
    >> >> >> >statement...so
    >> >> >> >> I took that out.
    >> >> >> >>
    >> >> >> >> Then it states that I don't have s1.serialnum, yet when I put

    it
    >> in,
    >> >> it
    >> >> >> >gives
    >> >> >> >> me a 'no group by clause' error.
    >> >> >> >>
    >> >> >> >> Also, there is an error on the e.serialnum line.
    >> >> >> >>
    >> >> >> >> Obviously, you can tell that I'm, by no means, on expert with

    >SQL,
    >> >> so
    >> >> >> even
    >> >> >> >> by playing around, I can't get the final product.
    >> >> >> >>
    >> >> >> >> Thanx again, Cary
    >> >> >> >>
    >> >> >> >>
    >> >> >> >> "Cary R" <cary.rotman@intria.com> wrote:
    >> >> >> >> >
    >> >> >> >> >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?
    >> >> >> >> >>> >> >

    >>

    >
    >



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