Re: using DISTINCT after the ORDER BY clause has been applied

[Yashesh Bhatia]

On 3/14/07, Bill Guion [EMAIL PROTECTED] wrote:

At 11:33 PM + 3/13/07, [EMAIL PROTECTED] wrote:

Date: Tue, 13 Mar 2007 20:56:08 +0530
To: mysql@lists.mysql.com
From: Yashesh Bhatia [EMAIL PROTECTED]
Subject: using DISTINCT after the ORDER BY clause has been applied
Message-ID: [EMAIL PROTECTED]

Hello:

I had a quick question on using the DISTINCT clause in a SELECT query.

I have the following table which stores webpages viewed

table: page_viewed
page_idint   unsignedpage id of the page viewed
user_id int   unsigneduser id of the page viewed
ts timestamptimestamp of the page view.

Now i need to query the most recently viewed distinct pages and i have
the following data


page_id user_id ts

1  1   2007-03-13 20:40:46
2  1   2007-03-13 20:40:53
2  1   2007-03-13 20:41:01
1  1   2007-03-13 20:41:10


so basically i tried to write a query for recently viewed (for user_id
1) as follows

SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC

however, this does not give me the result as i needed, i'd like to have it as

page_id

1
2

but the output is


page_id

2
1

therefore the DISTINCT clause would be first used to filter the rows
and then the ORDER BY would be applied, is there anyway to specify
that DISTINCT be applied after the ORDER BY clause ? if not, any other
way i could retrieve the above data ?

Thanks.

Yashesh Bhatia.




It looks to me as if your query returned exactly what you asked for.
It found the first two rows (other rows are not distinct), and then
ordered them in descending order by time stamp. Descending is largest
to smallest. TS for row 2 is larger than TS for row 1.



thx bill. yep. the DISTINCT is applied to the query first and then the
ORDER BY hence it takes the 1st 2 rows and then applies the ORDER BY.
however, in the application i need to get the rows ORDER BY ts DESC
first and then remove duplicates.

so it'd take the 4 rows and order them by ts desc

page_id

1
2
2
1

and then remove duplicates to give

page_id

1
2


any query to get the above desired result ?

thx.

yashesh bhatia


  -= Bill =-
--

You were born with all you need to win at life.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--

Go Pre
http://www2.localaccess.com/rlalonde/pre.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: using DISTINCT after the ORDER BY clause has been applied

[Yashesh Bhatia]

hi bill..

thx a lot for that tip. it worked fine.. this is the final one

SELECT DISTINCT t1.page_id
FROM (
SELECT page_id
FROM page_viewed
ORDER BY ts DESC
)t1

thanks.

yashesh bhatia.

On 3/14/07, Bill Guion [EMAIL PROTECTED] wrote:

At 8:48 PM +0530 3/14/07, Yashesh Bhatia wrote:

On 3/14/07, Bill Guion [EMAIL PROTECTED] wrote:
At 11:33 PM + 3/13/07, [EMAIL PROTECTED] wrote:

Date: Tue, 13 Mar 2007 20:56:08 +0530
To: mysql@lists.mysql.com
From: Yashesh Bhatia [EMAIL PROTECTED]
Subject: using DISTINCT after the ORDER BY clause has been applied
Message-ID: [EMAIL PROTECTED]

Hello:

 I had a quick question on using the DISTINCT clause in a SELECT query.

I have the following table which stores webpages viewed

table: page_viewed
page_idint   unsignedpage id of the page viewed
user_id int   unsigneduser id of the page viewed
ts timestamptimestamp of the page view.

Now i need to query the most recently viewed distinct pages and i have
the following data


page_id user_id ts

1  1   2007-03-13 20:40:46
2  1   2007-03-13 20:40:53
2  1   2007-03-13 20:41:01
1  1   2007-03-13 20:41:10


so basically i tried to write a query for recently viewed (for user_id
1) as follows

SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC

How about some variation of:

SELECT DISTICNT page_id from
(SELECT page_id, user_id, ts
FROM page_viewed
ORDER BY ts DESC);

Some how you will have to sort the input to the SELECT DISTINCT first.

  -= Bill =-

  
however, this does not give me the result as i needed, i'd like to
have it as

page_id

1
2

but the output is


page_id

2
1

therefore the DISTINCT clause would be first used to filter the rows
and then the ORDER BY would be applied, is there anyway to specify
that DISTINCT be applied after the ORDER BY clause ? if not, any other
way i could retrieve the above data ?

Thanks.

Yashesh Bhatia.


It looks to me as if your query returned exactly what you asked for.
It found the first two rows (other rows are not distinct), and then
ordered them in descending order by time stamp. Descending is largest
to smallest. TS for row 2 is larger than TS for row 1.


thx bill. yep. the DISTINCT is applied to the query first and then the
ORDER BY hence it takes the 1st 2 rows and then applies the ORDER BY.
however, in the application i need to get the rows ORDER BY ts DESC
first and then remove duplicates.

so it'd take the 4 rows and order them by ts desc

page_id

1
2
2
1

and then remove duplicates to give

page_id

1
2


any query to get the above desired result ?

thx.

yashesh bhatia

   -= Bill =-
--

You were born with all you need to win at life.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--

Go Pre
http://www2.localaccess.com/rlalonde/pre.htm



--

Read on a lawyer's tombstone: Motion denied.






--

Go Pre
http://www2.localaccess.com/rlalonde/pre.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

using DISTINCT after the ORDER BY clause has been applied

[Yashesh Bhatia]

Hello:

  I had a quick question on using the DISTINCT clause in a SELECT query.

I have the following table which stores webpages viewed

table: page_viewed
page_id int unsigned page id of the page viewed
user_id int unsigned user id of the page viewed
ts timestamp timestamp of the page view.

Now i need to query the most recently viewed distinct pages and i have
the following data

page_id  user_id ts
1   1   2007-03-13 20:40:46
2   1   2007-03-13 20:40:53
2   1   2007-03-13 20:41:01
1   1   2007-03-13 20:41:10


SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC


Go Pre
http://www2.localaccess.com/rlalonde/pre.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

using DISTINCT after the ORDER BY clause has been applied

[Yashesh Bhatia]

Hello:

  I had a quick question on using the DISTINCT clause in a SELECT query.

I have the following table which stores webpages viewed

table: page_viewed
page_idint   unsignedpage id of the page viewed
user_id int   unsigneduser id of the page viewed
ts timestamptimestamp of the page view.

Now i need to query the most recently viewed distinct pages and i have
the following data


page_id  user_id ts

1   1   2007-03-13 20:40:46
2   1   2007-03-13 20:40:53
2   1   2007-03-13 20:41:01
1   1   2007-03-13 20:41:10


so basically i tried to write a query for recently viewed (for user_id
1) as follows

SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC

however, this does not give me the result as i needed, i'd like to have it as

page_id

1
2

but the output is


page_id

2
1

therefore the DISTINCT clause would be first used to filter the rows
and then the ORDER BY would be applied, is there anyway to specify
that DISTINCT be applied after the ORDER BY clause ? if not, any other
way i could retrieve the above data ?

Thanks.

Yashesh Bhatia.


Go Pre
http://www2.localaccess.com/rlalonde/pre.htm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

using DISTINCT after the ORDER BY clause has been applied

[Bill Guion]

At 11:33 PM + 3/13/07, [EMAIL PROTECTED] wrote:


Date: Tue, 13 Mar 2007 20:56:08 +0530
To: mysql@lists.mysql.com
From: Yashesh Bhatia [EMAIL PROTECTED]
Subject: using DISTINCT after the ORDER BY clause has been applied
Message-ID: [EMAIL PROTECTED]

Hello:

   I had a quick question on using the DISTINCT clause in a SELECT query.

I have the following table which stores webpages viewed

table: page_viewed
page_idint   unsignedpage id of the page viewed
user_id int   unsigneduser id of the page viewed
ts timestamptimestamp of the page view.

Now i need to query the most recently viewed distinct pages and i have
the following data


page_id  user_id ts

1   1   2007-03-13 20:40:46
2   1   2007-03-13 20:40:53
2   1   2007-03-13 20:41:01
1   1   2007-03-13 20:41:10


so basically i tried to write a query for recently viewed (for user_id
1) as follows

SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC

however, this does not give me the result as i needed, i'd like to have it as

page_id

1
2

but the output is


page_id

2
1

therefore the DISTINCT clause would be first used to filter the rows
and then the ORDER BY would be applied, is there anyway to specify
that DISTINCT be applied after the ORDER BY clause ? if not, any other
way i could retrieve the above data ?

Thanks.

Yashesh Bhatia.


It looks to me as if your query returned exactly what you asked for. 
It found the first two rows (other rows are not distinct), and then 
ordered them in descending order by time stamp. Descending is largest 
to smallest. TS for row 2 is larger than TS for row 1.


 -= Bill =-
--

You were born with all you need to win at life.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Selecting several columns using DISTINCT

[Michael Collins]

At 4:46 PM +0100 2/3/02, Joseph Bueno wrote:
Michael Collins a écrit :
   I am trying to normalize some MySQL tables and need to move a few
  columns from one table to another while maintaining the relationship
   between the two.
   SELECT DISTINCT ProductID

  (SELECT
  ProductID,
  SmImage,
  ProductName,
  Description,
  LgImage
   WHERE ProductID=ProductID)
   FROM productSKU

   How do I rewrite this query to get what I am after?

Have you tried :
SELECT DISTINCT ProductID,SmImage,ProductName,Description,LgImage
   FROM productSKU ?

If all records with same ProductID have also the same values for
other selected fields it should work; otherwise, your normalization
has some problem.

Regards
--
Joseph Bueno


Unfortunately not, the Description differs. You are correct there is
a problem with normalization and that is what I am trying to fix. I
need to select the unique row based just on the ProductID column,
then move this foreign key to a record in a new table where ProductID
becomes the key. At the same time I want to import along with
ProductID a few more field values (which I can then remove from the
productSKU table).

--
Michael
__
||| Michael Collins   |||
||| Kuwago Web Services   |||  mailto:[EMAIL PROTECTED]
||| Seattle, WA, USA  |||  http://www.lassodev.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Selecting several columns using DISTINCT

[Michael Collins]

I am trying to normalize some MySQL tables and need to move a few 
columns from one table to another while maintaining the relationship 
between the two. I realize I cannot do a subselect, but in effect 
what I am wanting to do is to select the Distinct rows for the column 
ProductID and then select the columns from the rows that are found by 
the DISTINCT. This is the kind of query I have in mind:

SELECT DISTINCT ProductID

(SELECT
ProductID,
SmImage,
ProductName,
Description,
LgImage
WHERE ProductID=ProductID)

FROM productSKU

How do I rewrite this query to get what I am after?

-- 
Michael
__
||| Michael Collins   |||
||| Kuwago Web Services   |||  mailto:[EMAIL PROTECTED]
||| Seattle, WA, USA  |||  http://www.lassodev.com

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

using DISTINCT

[Jari Mäkelä]

Hi,

Is it possible to get all data; SELECT * FROM and still get some fields 
filtered by DISTINCT?

Jari Mäkelä



database,sql,query,table


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php