Re: complicated query | no Sub query

[Anoop kumar V]

Thanks Peter - you gave me some ideas...
here is what I have so far (simplified for simplification..)

select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log t2
where t1.id_secr_rqst=t2.id_secr_rqst
and t1.dt_aud_rec  t2.dt_aud_rec
group by t1.id_secr_rqst

but the problem is that it only returns the record related to the second 
largest date for each id_secr_rqst.

any suggestions how to get both the second and the largest date records in 
the same query?

Thanks,
Anoop


On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
 
 Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
 
  well - actually it might not be the last 2 days - i just want 2 of
  the latest records for every task regardless of what date it is in
  the table.
 
 Okay, now I think I understand what you need - and if I am correct, this
 looks like one of the more exotic querys to me, but then again, I'm not 
 like
 the SQL king around, but let me give it a shot:
 
 SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec)
 Latest, MAX(dt_aud_rec) NoSoLatest
 FROM isr2_aud_log t1, isr2_aud_log t2
 WHERE t1.id_secr_rqst = t2.id_secr_rqst
 AND t1.name_rec_type='Exception Resource'
 AND dt_aud_rec = Latest
 OR dt_aud_rec = NoSoLatest
 HAVING Latest  NoSoLatest
 GROUP BY t1.id_secr_rqst
 ORDER by t1.dt_aud_rec DESC;
 
 I am not 100% sure about the syntax, but you might get the idea.
 
 Peter Normann
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Thanks and best regards,
Anoop

Re: complicated query | no Sub query

[SGreen]

Anoop kumar V [EMAIL PROTECTED] wrote on 05/24/2005 03:02:11 PM:

 Thanks Peter - you gave me some ideas...
 here is what I have so far (simplified for simplification..)
 
 select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
t2
 where t1.id_secr_rqst=t2.id_secr_rqst
 and t1.dt_aud_rec  t2.dt_aud_rec
 group by t1.id_secr_rqst
 
 but the problem is that it only returns the record related to the second 

 largest date for each id_secr_rqst.
 
 any suggestions how to get both the second and the largest date records 
in 
 the same query?
 
 Thanks,
 Anoop
 
 
 On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
  
  Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
  
   well - actually it might not be the last 2 days - i just want 2 of
   the latest records for every task regardless of what date it is in
   the table.
  
  Okay, now I think I understand what you need - and if I am correct, 
this
  looks like one of the more exotic querys to me, but then again, I'm 
not 
  like
  the SQL king around, but let me give it a shot:
  
  SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
MAX(dt_aud_rec)
  Latest, MAX(dt_aud_rec) NoSoLatest
  FROM isr2_aud_log t1, isr2_aud_log t2
  WHERE t1.id_secr_rqst = t2.id_secr_rqst
  AND t1.name_rec_type='Exception Resource'
  AND dt_aud_rec = Latest
  OR dt_aud_rec = NoSoLatest
  HAVING Latest  NoSoLatest
  GROUP BY t1.id_secr_rqst
  ORDER by t1.dt_aud_rec DESC;
  
  I am not 100% sure about the syntax, but you might get the idea.
  
  Peter Normann
  
  
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe: 
http://lists.mysql.com/[EMAIL PROTECTED]
  
  
 
 
 -- 
 Thanks and best regards,
 Anoop

I would solve this query by first constructing a table that contains the 
information I need to identify the two most recent records (tasks). SINCE 
YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
forced to make up nearly every part of my answer. And because you want it 
to be cross-database portable, I won't be able to use the group-wize 
autonumber trick. However this will use a technique twice like the 
group-wize-maximum technique (described here: 
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html )

CREATE TEMPORARY TABLE tmpMaxDates (
task_id varchar(10) not null,
task_date date not null,
INDEX(task_id, task_date) 
);

INSERT tmpMax (task_id, task_date)
SELECT task_ID, max(task_date)
FROM tasktable
GROUP BY task_ID;

#now collect the max(PK) value for each task_id/task_date pair

CREATE TEMPORARY TABLE tmpRecordsToProcess (
task_id varchar(10) not null,
task_date date not null,
task_pk int not null
INDEX(task_pk) 
);

INSERT tmpRecordsToProcess rtp (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
GROUP BY tt.task_id, tt.task_date;

# now to get the second record back

DELETE FROM tmpMaxDates;

INSERT tmpMaxDates (task_id, task_date)
SELECT tt.task_id, max(tt.task_date)
FROM tasktable tt
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_pk is null
GROUP BY tt.task_id;


INSERT tmpRecordsToProcess (task_id, task_date, task_pk)
SELECT tt.task_id, tt.task_date, max(tt.pk)
FROM tasktable tt
INNER JOIN tmpMaxDates md
on tt.task_id = md.task_id
AND tt.task_date = md.task_date
LEFT JOIN tmpRecordsToProcess rtp
ON rtp.task_pk = tt.PK
WHERE rtp.task_PK is null
GROUP BY tt.task_id, tt.task_date;

Now (assuming I am not too hosed-up today) you should be able to process 
against tmpRecordsToProcess (using the task_pk field) to limit your 
queries to just those PK values you have identified as being the two most 
recent for each task (assuming a higher PK value is more recent than a 
lower one for the same task/date pair). This would have been much easier 
to code if we had been able to use the group-wize auto-increment feature 
of MyISAM. You can repeat the last 3 statements as often as you wish in 
order to build a larger most recent list. I am sure that if I made any 
logical errors, fresher minds on the list will catch them as I am nearing 
the end of a rather long day and could have easily mis-typed something.

To summarize: tmpRecordsToProcess should contain a list of the primary key 
values of the two most recent records for each task.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: complicated query | no Sub query

[Anoop kumar V]

My profound apologies

here is the table create structure.
the biggest problem i think is that this table does not have any primary 
keys or atleast unique columns: (I think joins require unique columns)

mysql show create table isr2_aud_log\G
*** 1. row ***
Table: isr2_aud_log
Create Table: CREATE TABLE `isr2_aud_log` (
`id_secr_rqst` varchar(64) NOT NULL default '',
`dt_aud_rec` datetime NOT NULL default '-00-00 00:00:00',
`name_rec_type` varchar(30) default NULL,
`cd_rqst_type` varchar(15) default NULL,
`id_user` varchar(10) default NULL,
`name_user_first` varchar(40) default NULL,
`name_user_mid` varchar(40) default NULL,
`name_user_lst` varchar(40) default NULL,
`cd_user_div` varchar(10) default NULL,
`cd_user_cst_cntr` varchar(15) default NULL,
`id_actnee` varchar(10) default NULL,
`name_actnee_first` varchar(40) default NULL,
`name_actnee_mid` varchar(40) default NULL,
`name_actnee_lst` varchar(40) default NULL,
`cd_pltfrm` varchar(10) default NULL,
`cd_rsrc_sub_type` varchar(10) default NULL,
`cd_actn` varchar(10) default NULL,
`cd_rsrc_div` varchar(10) default NULL,
`name_grp` varchar(70) default NULL,
`name_svr` varchar(70) default NULL,
`name_rsrc_1` varchar(70) default NULL,
`name_rsrc_2` varchar(70) default NULL,
`name_rsrc_3` varchar(70) default NULL,
`name_rsrc_4` varchar(70) default NULL,
`name_rsrc_5` varchar(70) default NULL,
`cd_sts_apprl` varchar(30) default NULL,
`cd_prcsg_type` varchar(10) default NULL,
`text_actnee_cmnts` varchar(255) default NULL,
`text_spcl_instn` varchar(255) default NULL,
`dt_lst_updt` datetime default NULL,
`id_user_lst_updt` varchar(8) default NULL
) TYPE=MyISAM

I did read your response/answer to my problem and being a newbie, I found it 
quite complicated for me to follow. Does it really require more than just 1 
or 2 simple select queries to pull out rows ( unique id_secr_rqst - 2 of 
them for each) which have the max(dt_aud_rec) and second max(dt_aud_rec)? I 
mean simple queries with joins. I cannot use sub queries.

I am assured that the table will have no more than a 1000 records and after 
my initial filtering I will have to deal with 100 records maximum. So 
performance is not a problem at all.

If SGreen's response is the only one then its ok - I will try to follow that 
- else I think it has room for simplication a bit.

Thanks,
Anoop

On 5/24/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
 
 Anoop kumar V [EMAIL PROTECTED] wrote on 05/24/2005 03:02:11 PM:
 
  Thanks Peter - you gave me some ideas...
  here is what I have so far (simplified for simplification..)
  
  select t2.dt_aud_rec, t1.id_secr_rqst from isr2_aud_log t1, isr2_aud_log 
 t2
  where t1.id_secr_rqst=t2.id_secr_rqst
  and t1.dt_aud_rec  t2.dt_aud_rec
  group by t1.id_secr_rqst
  
  but the problem is that it only returns the record related to the second 
 
  largest date for each id_secr_rqst.
  
  any suggestions how to get both the second and the largest date records 
 in 
  the same query?
  
  Thanks,
  Anoop
  
  
  On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
   
   Anoop kumar V mailto:[EMAIL PROTECTED] wrote:
   
well - actually it might not be the last 2 days - i just want 2 of
the latest records for every task regardless of what date it is in
the table.
   
   Okay, now I think I understand what you need - and if I am correct, 
 this
   looks like one of the more exotic querys to me, but then again, I'm 
 not 
   like
   the SQL king around, but let me give it a shot:
   
   SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, 
 MAX(dt_aud_rec)
   Latest, MAX(dt_aud_rec) NoSoLatest
   FROM isr2_aud_log t1, isr2_aud_log t2
   WHERE t1.id_secr_rqst = t2.id_secr_rqst
   AND t1.name_rec_type='Exception Resource'
   AND dt_aud_rec = Latest
   OR dt_aud_rec = NoSoLatest
   HAVING Latest  NoSoLatest
   GROUP BY t1.id_secr_rqst
   ORDER by t1.dt_aud_rec DESC;
   
   I am not 100% sure about the syntax, but you might get the idea.
   
   Peter Normann
   
   
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe: 
 http://lists.mysql.com/[EMAIL PROTECTED]
   
   
  
  
  -- 
  Thanks and best regards,
  Anoop
  
 I would solve this query by first constructing a table that contains the 
 information I need to identify the two most recent records (tasks). SINCE 
 YOU HAVE YET TO POST AN ACTUAL TABLE STRUCTURE (shame on you), I will be 
 forced to make up nearly every part of my answer. And because you want it to 
 be cross-database portable, I won't be able to use the group-wize autonumber 
 trick. However this will use a technique twice like the group-wize-maximum 
 technique (described here: 
 http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html ) 
 
 CREATE TEMPORARY TABLE tmpMaxDates ( 
 task_id varchar(10) not null, 
 task_date date not null, 
 INDEX(task_id, task_date) 
 ); 
 
 INSERT tmpMax (task_id, task_date) 
 SELECT task_ID, 

RE: complicated query | no Sub query

[Peter Normann]

Hi Anoop

Try:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst =
t2.id_secr_rqst AND
t1.name_rec_type='Exception Resource' 
ORDER  by t1.dt_aud_rec DESC
LIMIT 2;

Peter Normann


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

Re: complicated query | no Sub query

[Anoop kumar V]

Thanks Peter - but I see two issues:

1. It returns data about only one id_secr_rqst - I want it to return data 
about every id_secr_rqst in the table.
2. Limit IMO is mysql specific (I hope I am wrong) is there something 
generic so I dont need to bother about which database I am running it 
against.

Thanks,
Anoop

On 5/23/05, Peter Normann [EMAIL PROTECTED] wrote:
 
 Hi Anoop
 
 Try:
 
 SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec
 FROM isr2_aud_log t1, isr2_aud_log t2
 WHERE t1.id_secr_rqst =
 t2.id_secr_rqst AND
 t1.name_rec_type='Exception Resource'
 ORDER by t1.dt_aud_rec DESC
 LIMIT 2;
 
 Peter Normann
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Thanks and best regards,
Anoop

RE: complicated query | no Sub query

[Peter Normann]

Anoop kumar V mailto:[EMAIL PROTECTED] wrote:

 1. It returns data about only one id_secr_rqst - I want it to return
 data about every id_secr_rqst in the table.

So, if I understand you correctly (sorry, having a bad day), you want all
records for the past two days?

Assuming this, you could use something like:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec 
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst = t2.id_secr_rqst
AND t1.name_rec_type='Exception Resource' 
AND dt_aud_rec  CURDATE() - 2;
ORDER  by t1.dt_aud_rec DESC;

 2. Limit IMO is mysql specific (I hope I am wrong) is there something
 generic so I dont need to bother about which database I am running it
 against.

As far as I know it is MySql specific. SQL Server's equivalent is SELECT TOP
2 or something.

Peter Normann


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

RE: complicated query | no Sub query

[Peter Normann]

Anoop kumar V mailto:[EMAIL PROTECTED] wrote:

 well - actually it might not be the last 2 days - i just want 2 of
 the latest records for every task regardless of what date it is in
 the table.  

Okay, now I think I understand what you need - and if I am correct, this
looks like one of the more exotic querys to me, but then again, I'm not like
the SQL king around, but let me give it a shot:

SELECT t1.id_secr_rqst, t2.name_rec_type, t1.dt_aud_rec, MAX(dt_aud_rec)
Latest, MAX(dt_aud_rec) NoSoLatest
FROM isr2_aud_log t1, isr2_aud_log t2
WHERE t1.id_secr_rqst = t2.id_secr_rqst
AND t1.name_rec_type='Exception Resource'
AND dt_aud_rec = Latest
OR dt_aud_rec = NoSoLatest
HAVING Latest  NoSoLatest
GROUP BY t1.id_secr_rqst
ORDER by t1.dt_aud_rec DESC;

I am not 100% sure about the syntax, but you might get the idea.

Peter Normann


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

Re: Complicated Query

[Ian Sales (DBA)]

Ron Watson wrote:
This works, but only if a title and a role exists for the company member.
Basically, I want to display the season name from tbl Season, then the show
title from tbl Shows, then the roles from cast and titles from
production_team for the company member. 

 

- use LEFT JOINs.
- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
|  If your DBA is busy all the time... |
|   ...he's not doing his job properly |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Complicated query problem

[beacker]

The query as written works just fine although I'm certain there's got to be
a more efficient way of doing the same thing.  I'm relatively new to MySQL
so I took the brute force approach.

My problem is that I want to produce totals of each of the columns and can't
figure out how to do it.  Any suggestions on how I can do this?

 Might I suggest a mapping table relating speed to the labels desired
and the speed ratings (int - strings).  That way it's extensible and
can easily be joined to the original query?
   Brad Eacker ([EMAIL PROTECTED])



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

Re: Complicated query. query help

[Roger Baklund]

* TimeWalker 
 I've been trying to work out a complicated query to select and 
 sum  multiple columns in one select using a condition and multi 
 column group by
 
 
 What I expected to get was ONE row  with the columns summed .
 
 this query returns 5 rows
 
 SELECT sum(`AllTheWeb`),sum(`AltaVista`),sum(`AOL`),sum(`Ask`),sum(`Ask 
 Jeeves`),sum(`Cest 
 trouve`),sum(`DirectHit`),sum(`DMOZ`),sum(`Dogpile`),sum(`Euroseek
 `),sum(`Excite`),sum(`Fireball`),sum(`FrancitT`),sum(`Go2Net`),sum
 (`Google`),sum(`Hotbot`),sum(`Kanoodle`),sum(`LBB`),sum(`Libertysu
 rf`),sum(`Lokace`),sum(`Lycos`),sum(`Mamma`),sum(`MegaSpider`),sum
 (`MetaCrawler`),sum(`MetaGer`),sum(`MSN`),sum(`NBCI`),sum(`Netscap
 e`),sum(`Nomade`),sum(`NorthernLight`),sum(`Overture`),sum(`Spray`
 ),sum(`Terra`),sum(`Vindex.nl`),sum(`Voila`),sum(`Web.de`),sum(`We
 bCrawler`),sum(`WebSearch`),sum(`Yahoo`) 
 FROM engine where sitenum = 2365602 GROUP BY 
 `AllTheWeb`,`AltaVista`,`AOL`,`Ask`,`Ask Jeeves`,`Cest 
 trouve`,`DirectHit`,`DMOZ`,`Dogpile`,`Euroseek`,`Excite`,`Fireball
 `,`FrancitT`,`Go2Net`,`Google`,`Hotbot`,`Kanoodle`,`LBB`,`Libertys
 urf`,`Lokace`,`Lycos`,`Mamma`,`MegaSpider`,`MetaCrawler`,`MetaGer`
 ,`MSN`,`NBCI`,`Netscape`,`Nomade`,`NorthernLight`,`Overture`,`Spra
 y`,`Terra`,`Vindex.nl`,`Voila`,`Web.de`,`WebCrawler`,`WebSearch`,`Yahoo`

Have you tried this:

  SELECT sum(... WHERE sitenum = 2365602 GROUP BY sitenum

...should return one row...

-- 
Roger
sql

-
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

RE: Complicated Query?

[Rick Emery]

Needs to be done programmatically

-Original Message-
From: Jerry Rehak [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 10:53 AM
To: [EMAIL PROTECTED]
Subject: Complicated Query?


I have a table with the columns names and id.  I want to be able to find all
names with an id of '03' that do not have other records with id values of
'10','20' or '37'.

Is this even possible to do?

name   id
a03
a11
a12
a13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
b03
b10 I don't want 'b' because it has a 10
c04
c11
c20 I don't want 'c' because it doesn't have a 03
d03 I want 'd' because it has a 03 and no other records

Thanks for your help


-
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

-
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

RE: Complicated Query?

[Roger Baklund]

* Jerry Rehak
 I have a table with the columns names and id.  I want to be able
 to find all
 names with an id of '03' that do not have other records with id values of
 '10','20' or '37'.

 Is this even possible to do?

 name   id
 a  03
 a  11
 a  12
 a  13 I want 'a' because it has a 03 and not a 10, a 20, OR 37
 b  03
 b  10 I don't want 'b' because it has a 10
 c  04
 c  11
 c  20 I don't want 'c' because it doesn't have a 03
 d  03 I want 'd' because it has a 03 and no other records

This can be done with a simple LEFT JOIN:

SELECT t1.*
  FROM table AS t1
  LEFT JOIN table AS t2 ON
t2.name=t1.name AND
t2.id IN ('10','20','37')
  WHERE t1.id='03' AND t2.id IS NULL;

We select the rows we want from t1, left join with the rows we don't want
(t2), and put as a condition in the where clause that we only want rows
where t2 was not found.

--
Roger


-
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

Re: complicated query

[Milo Stefani]

try
ORDER BY (Category = 'Other'), Category, Subcategory
It should work, (I hope ^_-;;;)

- Original Message -
From: Jeff Shipman - SysProg [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, April 20, 2001 8:38 PM
Subject: complicated query


 I would like to do something similar to an ORDER BY
 in one of my select statements, but I'mt not sure
 how to do something as complicated as this:

 I have two columns, category and subcategory, that
 I am retrieving. I would like category and subcategory
 to be sorted alphabetically. This is easy with an
 'ORDER BY 1 2', but I would like categories that
 are named 'other' to be put off until the end. So,
 I'd get something like this:

 abcd
 ghikj
 z
 other

 Is there a way to do this type of query? Thanks in
 advance.

 Jeff Shipman   E-Mail: [EMAIL PROTECTED]
 Systems Programmer Phone: (505) 835-5748
 NMIMT Computer Center  http://www.nmt.edu/~jeff



 -
 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


-
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

RE: complicated query

[Braxton Robbason]

you want a function that prepends the letter a to the category/subcategory
names that are not other.  then you order by that function, but do not
display it.

select category,subcategory from foo2
order by if(category=
'other','zz',concat('a',category));
i.e. everything except other begins with an a as far as the order by is
concerned, while other is zz for sorting purposes.


-Original Message-
From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 20, 2001 2:39 PM
To: [EMAIL PROTECTED]
Subject: complicated query


I would like to do something similar to an ORDER BY
in one of my select statements, but I'mt not sure
how to do something as complicated as this:

I have two columns, category and subcategory, that
I am retrieving. I would like category and subcategory
to be sorted alphabetically. This is easy with an
'ORDER BY 1 2', but I would like categories that
are named 'other' to be put off until the end. So,
I'd get something like this:

abcd
ghikj
z
other

Is there a way to do this type of query? Thanks in
advance.

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff



-
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



-
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

Re: complicated query

[Steve Werby]

"Jeff Shipman - SysProg" [EMAIL PROTECTED] wrote:
 I would like to do something similar to an ORDER BY
 in one of my select statements, but I'mt not sure
 how to do something as complicated as this:

 I have two columns, category and subcategory, that
 I am retrieving. I would like category and subcategory
 to be sorted alphabetically. This is easy with an
 'ORDER BY 1 2', but I would like categories that
 are named 'other' to be put off until the end. So,
 I'd get something like this:

 abcd
 ghikj
 z
 other

 Is there a way to do this type of query? Thanks in
 advance.

If you make the category field an ENUM type then it will automatically be
sorted in the same order as the order of the ENUM values.  Another option
would be to create an additional column (or 2 columns if the same problem
occurs in "subcategory") and assign records with a category of "other" a
value of 1 and assign all other records a default value of 0.  Let's call
the new field "order_1" Then you could construct an ORDER BY clause like:

ORDER BY order_1, category...

--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.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

Re: complicated query

[Rick Pasotto]

On Fri, Apr 20, 2001 at 03:39:59PM -0400, Steve Werby wrote:
 "Jeff Shipman - SysProg" [EMAIL PROTECTED] wrote:
  I would like to do something similar to an ORDER BY
  in one of my select statements, but I'mt not sure
  how to do something as complicated as this:
 
  I have two columns, category and subcategory, that
  I am retrieving. I would like category and subcategory
  to be sorted alphabetically. This is easy with an
  'ORDER BY 1 2', but I would like categories that
  are named 'other' to be put off until the end. So,
  I'd get something like this:
 
  abcd
  ghikj
  z
  other
 
  Is there a way to do this type of query? Thanks in
  advance.
 
 If you make the category field an ENUM type then it will automatically be
 sorted in the same order as the order of the ENUM values.  Another option
 would be to create an additional column (or 2 columns if the same problem
 occurs in "subcategory") and assign records with a category of "other" a
 value of 1 and assign all other records a default value of 0.  Let's call
 the new field "order_1" Then you could construct an ORDER BY clause like:
 
 ORDER BY order_1, category...

You don't need to create another column, just use:

ORDER BY category = 'other', category

-- 
"Freedom is just chaos with better lighting."
-- Alan Dean Foster
   Rick Pasotto email: [EMAIL PROTECTED]

-
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

Re: complicated query

[Jeff Shipman - SysProg]

}
} You don't need to create another column, just use:
}
} ORDER BY category = 'other', category
}

Are you sure this works? My query is:

select category,subcategory from categories ORDER BY
category = 'other', category;

And I get:

ERROR 1064: You have an error in your SQL syntax
near '= 'other', category' at line 1

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff



-
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

RE: complicated query

[Jeff Shipman - SysProg]

This does not work for me. It returns syntax
errors.

Jeff Shipman   E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center  http://www.nmt.edu/~jeff

On Fri, 20 Apr 2001, Braxton Robbason wrote:

} you want a function that prepends the letter a to the category/subcategory
} names that are not other.  then you order by that function, but do not
} display it.
}
} select category,subcategory from foo2
} order by if(category=
} 'other','zz',concat('a',category));
} i.e. everything except other begins with an a as far as the order by is
} concerned, while other is zz for sorting purposes.
}
}
} -Original Message-
} From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
} Sent: Friday, April 20, 2001 2:39 PM
} To: [EMAIL PROTECTED]
} Subject: complicated query
}
}
} I would like to do something similar to an ORDER BY
} in one of my select statements, but I'mt not sure
} how to do something as complicated as this:
}
} I have two columns, category and subcategory, that
} I am retrieving. I would like category and subcategory
} to be sorted alphabetically. This is easy with an
} 'ORDER BY 1 2', but I would like categories that
} are named 'other' to be put off until the end. So,
} I'd get something like this:
}
} abcd
} ghikj
} z
} other
}
} Is there a way to do this type of query? Thanks in
} advance.
}
} Jeff Shipman   E-Mail: [EMAIL PROTECTED]
} Systems Programmer Phone: (505) 835-5748
} NMIMT Computer Center  http://www.nmt.edu/~jeff
}
}
}
} -
} 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
}
}
}
} -
} 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
}


-
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

Re: complicated query

[Rick Pasotto]

On Fri, Apr 20, 2001 at 03:09:32PM -0600, Jeff Shipman - SysProg wrote:
 }
 } You don't need to create another column, just use:
 }
 } ORDER BY category = 'other', category
 }
 
 Are you sure this works? My query is:
 
 select category,subcategory from categories ORDER BY
 category = 'other', category;
 
 And I get:
 
 ERROR 1064: You have an error in your SQL syntax
 near '= 'other', category' at line 1

Yes. And I of course tested before I posted. I also tried 

ORDER BY category in ('cat1','cat2'), category

which worked.

I'm running 3.23.36 on debian linux.

Well, actually, rather than create a new table, I used an existing one.
The actual statement was

select last_name from mizpah where first_name like 'fr%' order by
last_name = 'crosby', last_name;

-- 
"Moderation in temper is always a virtue; but moderation in
 principle is always a vice."
-- Thomas Paine, _The Rights of Man_ (1791)
   Rick Pasotto email: [EMAIL PROTECTED]

-
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