If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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
>

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
> >
>
>

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
> >
>
>

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
>> >
>>
>>
>
>

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
> >> >
> >>
> >>
> >
> >
>

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
>>> >
>>>
>>>
>>
>>
>

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
>>>> >
>>>>
>>>>
>>>
>>>
>>
>

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
>> >>>> >
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >
>>
>
>

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.

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
>