On Saturday, July 19, 2014 02:56:24 PM Reindl Harald wrote:
> > ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> > that corresponds to your MariaDB server version for the right syntax to
> > use
> > near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum
> > from p
Am 19.07.2014 13:45, schrieb Arup Rakshit:
> Here is my simple table
>
> MariaDB [tutorial]> select * from prices;
> ++--+--+
> | id | name | cost |
> ++--+--+
> | 1 | A| 1200 |
> | 2 | A| 2500 |
> | 3 | A| 3000 |
> | 4 | B| 5000 |
> | 5 | B| 7000
Hi,
you have to use `HAVING' instead of `WHERE' like this.
SELECT DISTINCT
`term`,
COUNT(*) AS count
FROM blp_sql_distinct_temp_table
GROUP BY `term`
HAVING count >= 5
ORDER BY count DESC;
put `HAVING' next of `GROUP BY'.
`WHERE' behaves at before aggregate of `GROUP BY'.
your SQL means like
- Original Message -
From: "Eric Anderson"
To:
Sent: Tuesday, October 20, 2009 4:05 PM
Subject: Re: Distinct max() and separate unique value
I'm trying to formulate a query on a Wordpress database that will give
me the highest 'object_id' with the
On Tue, 20 Oct 2009, DaWiz wrote:
I would try:
select max(object_id), term_taxonomy_id
group by term_taxonomy_id
order by term_taxonomy_id;
max(column) returns a single value so distinct is not needed.
The group by and order by should only have columns thaqt are displayed and
that are not agg
I would try:
select max(object_id), term_taxonomy_id
group by term_taxonomy_id
order by term_taxonomy_id;
max(column) returns a single value so distinct is not needed.
The group by and order by should only have columns thaqt are displayed and
that are not aggregate columns.
- Original Mes
exact
> columns interms of wat is suppliers which table is it coming from
> etc.
>
> -Original Message-
> From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
> Sent: Monday, December 22, 2008 5:16 PM
> To: Baron Schwartz; [MySQL]
> Subject: Re: Distinct Query Probl
On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
wrote:
> Hi,
>
> I've the following query which I'm having problems with. Basically I have
> a 5 tables as follows :
>
> MasterTB - Contains list of master records
> LookupTB - Contains relationship between MasterTB to ProductTB
> ContentTB - Contain
OK, I've made further progress by changing GROUP BY ProductTB.ProductID,
MasterTB.MasterID to GROUP BY MasterTB.MasterID.
However ProductTB.Supplier is showing the incorrect Supplier. Why is this ?
Thanks
Neil
On Mon, Dec 22, 2008 at 11:45 AM, Tompkins Neil <
neil.tompk...@googlemail.com> wrote
Hi,
If anyone could point me in the right direction, I'd be most grateful.
Thanks !
Neil
On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil wrote:
> Hi
>
> I'm having trouble trying to figure this out. Any help/example would be
> grateful.
>
> Thanks
> Neil
>
> On Sun, Dec 21, 2008 at 4:30 PM,
Hi
I'm having trouble trying to figure this out. Any help/example would be
grateful.
Thanks
Neil
On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz wrote:
> On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil
> wrote:
>
> > of products for all suppliers for a particular product. However I want
> to
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote:
> Basically each product is listed in the master table, and can have a number
> of suppliers linked to it (ProductTB). The query above will show me a list
> of products for all suppliers for a particular product. However I want to
> be able to
Never mind, I figured it out:
select distinct(concat(lat,lon)), lat, lon where
On Sep 13, 2006, at 6:57 AM, Brian Dunning wrote:
But if I do this, how do I still get lat and lon as two different
fields? This finds the right record set, but it returns both fields
concatenated into a sing
But if I do this, how do I still get lat and lon as two different
fields? This finds the right record set, but it returns both fields
concatenated into a single field.
On Sep 12, 2006, at 12:46 PM, Steve Musumeche wrote:
You could try using CONCAT:
select distinct(CONCAT(lat, long)) from
Why don't you just use a GROUP BY on lat,long?
> You could try using CONCAT:
>
> select distinct(CONCAT(lat, long)) from table where ...
>
> Steve Musumeche
> CIO, Internet Retail Connection
> [EMAIL PROTECTED]
>
>
>
> Brian Dunning wrote:
> > Lat & lon are two different fields. Either can be dupl
You could try using CONCAT:
select distinct(CONCAT(lat, long)) from table where ...
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Brian Dunning wrote:
Lat & lon are two different fields. Either can be duplicated, but not
both.
On Sep 12, 2006, at 12:33 PM, Steve Musume
Lat & lon are two different fields. Either can be duplicated, but not
both.
On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote:
Select DISTINCT(lat_long_field) from table where...
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Brian Dunning wrote:
Many different recor
Select DISTINCT(lat_long_field) from table where...
Steve Musumeche
CIO, Internet Retail Connection
[EMAIL PROTECTED]
Brian Dunning wrote:
Many different records will be returned though, I just don't want any
dupes where both lat/lon is the same.
:)
On Sep 12, 2006, at 12:20 PM, Hiep Nguye
Many different records will be returned though, I just don't want any
dupes where both lat/lon is the same.
:)
On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote:
select * from table where . limit 1
that would do it if you don't care which one it returns
JC
On Tue, 12 Sep 2006, Brian Dun
select * from table where . limit 1
that would do it if you don't care which one it returns
JC
On Tue, 12 Sep 2006, Brian Dunning wrote:
> I'm searching a database of geopoints, and when two records have the
> same latitude and longitude, I only want to return one of them -
> basically
Tanner Postert wrote:
I actually solved my own problem...
SELECT t1.item_id, t1.dt, t1.text ,t3.*
FROM table AS t1, table3 as t3
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;
becomes
SELECT t1.item_id, t1.dt, t1.text
FROM (table AS t1, table3 as t
I actually solved my own problem...
SELECT t1.item_id, t1.dt, t1.text ,t3.*
FROM table AS t1, table3 as t3
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;
becomes
SELECT t1.item_id, t1.dt, t1.text
FROM (table AS t1, table3 as t3)
LEFT JOIN table AS t
the below query worked great in mysql 3.23, but we just moved to 5.0 and it
broke, i can see that the join rules changed in 5.0, but i can't get the
right syntax to make this query work. any help would be appreciated.
On 6/28/06, Peter Brawley <[EMAIL PROTECTED]> wrote:
Tanner
>I am trying t
Mark Steudel wrote:
Is there a way get distinct results between pf and sf?
Select
pf.name,
sf.name
From
tblpropertyfeatures,
tblsuitefeatures
Inner Join tblfeatures AS pf ON tblpropertyfeatures.featureid = pf.id
Inner Join tblfeatures AS sf ON tblsuitefeatures.featureid = sf.id
Do you m
Tanner
>I am trying to group my results by the last activity on each row,
my query
>looks like this
>select text, dt, item_id from table
>where
>group by item_id
>order by dt DESC
SELECT t1.item_id, t1.dt, t1.text
FROM table AS t1
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AN
Use the MAX() function, like so:
select text, MAX(dt) as dt, item_id from table
where
group by item_id
order by dt DESC
Dan
On 6/28/06, Tanner Postert <[EMAIL PROTECTED]> wrote:
The situation is somewhat hard to describe, so please bare with me:
I am trying to group my results by the la
ROFLMAO -- Yep! It's been one of _those_ days!!!
Shawn
Michael Stassen <[EMAIL PROTECTED]> wrote on 08/17/2004 05:10:46
PM:
> Just a minor quibble: You don't need DISTINCT here, because the GROUP
BY
> will ensure distinct results. Probably just another fast-typing
symptom.
>
> Michael
>
Just a minor quibble: You don't need DISTINCT here, because the GROUP BY
will ensure distinct results. Probably just another fast-typing symptom.
Michael
[EMAIL PROTECTED] wrote:
oops! - typing too fast for my own good. The fixed query should have been:
SELECT DISTINCT E.Title, E.ID, max(C.Date
Thanks, that did the trick.
On Aug 17, 2004, at 12:34 PM, [EMAIL PROTECTED] wrote:
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
oops! - typing too fast for my own good. The fixed query should have been:
SELECT DISTINCT E.Title, E.ID, max(C.DateAdded) as maxdate
FROM Entries E, Comments C
WHERE C.EntryID = E.ID
GROUP BY E.Title, E.ID
ORDER BY maxdate desc
LIMIT 10
8-D
Shawn
[EMAIL PROTECTED] wrote on 08/17/2004 03:26:53
I don't think the engine is messing up here. I think you are getting
exactly what you asked for and I will explain why I think that.
You are asking for 3 things from 2 tables:
Entries.ID
, Entries.Title
, Comments.DateAdded.
If Entry #1 was called "My First Thread" and h
Ron Gilbert wrote:
I am trying to run this query...
select distinct E.Title, E.ID, C.DateAdded from
Entries E, Comments C
where C.EntryID = E.ID
order by C.DateAdded desc
limit 10
...to get the last 10 entries that had comments added to them, but the
DISTINCT is not returning distinct results. Wh
- Original Message -
From: "Claire Lee" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 03, 2004 11:28 AM
Subject: distinct based on two fields--please help
> I have a table like this
>
> name price type
> A10 1
> B30 2
> A20
From: Claire Lee [mailto:[EMAIL PROTECTED]
> I have a table like this
>
> name price type
> A10 1
> B30 2
> A20 1
> B20 2
>
> would like to distinct based on name and type, do a
> sum and get the following result
>
> name price type
> A
And I have a table like this
click send on accident?
A
Don't leave me hangin'!!
-- Tripp
--- Claire Lee <[EMAIL PROTECTED]> wrote:
> I have a table like this
>
> name price type
> A
>
>
>
> __
> Do you Yahoo!?
> New and Improved Yahoo
I think you still did it wrong, assuming the first column is IDs, but no
matter. How do you determine which row from Table2 is "the last record
inserted with that number."?
Michael
PhistucK wrote:
I'm posting it again coz I did it all wrong. so here it is again:
I got a little problem.
I want t
MAIL
PROTECTED]>
06/01/2004 02:28 Fax to:
the before and after data.
Jack
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 01, 2004 1:52 PM
To: Jack Coxen
Cc: MySQL List (E-mail)
Subject: Re: DISTINCT query
Jack,
I am not sure what you want your output to look like... If you want the
sa
Jack,
I am not sure what you want your output to look like... If you want the
same columns (the routerid with the counts of how many interfaces operate
at which speed) but broken down by unique descriptions that means to me
that you have multiple descriptions for each unique interface ID... Is th
try something like this:
select site_id, max(service_date), name from
site_service left join site on site.id =
site_service.site_id group by site_id;
--- Tom Beidler <[EMAIL PROTECTED]> wrote:
> I¹m having problems with a left join. I¹m not even
> sure if I should be using
> a left join.
>
>
On Friday 20 December 2002 13:35, Steve Vernon wrote:
> MySQL as the filter stupidly does not match SELECT
>
> Hiya,
> How do you get a disctinct value from one collumn only? I am basically
> using it to construct a dynamic menu, and need the menu names at a level.
>
> Items are in one tab
> The table has something like this:
>
> idnamekey
> 1 name1 key1
> 2 name2 key2
> 3 name3 key3
> 4 name1 key1
> 5 name 1 key1
> 6 name2 key2
>
>
> Now I want to search the table for all unique name/key combinations.
>
select name,key from table group b
Hello.
On Mon 2002-12-09 at 13:45:42 +0200, [EMAIL PROTECTED] wrote:
> Please can someone help me with this:
>
> I need to "filter" duplicate entries from my result query, but there's a
> twist...
>
[...]
> Now I want to search the table for all unique name/key combinations.
>
> I tried
> sel
On Mon, 2002-12-09 at 12:45, Petre Agenbag wrote:
> sql,query
[snip]
> I need to "filter" duplicate entries from my result query, but there's a
> twist...
[snip]
> Where name 1 key1 was obviously a typo, yet, I would ideally want to
> filter or flag this through some kind of logic => ( there is alr
Marc,
Saturday, October 05, 2002, 9:59:57 PM, you wrote:
> It seems that ORDER BY interferes with DISTINCT when
> I
> use the 2 in the same statement. When I append order
> by to the end of a MySQL statement, I get all the
> matches instead of the distinct ones. Is this normal
> behavior?
Could
--- [EMAIL PROTECTED] wrote:
> Your message cannot be posted because it appears to
> be either spam or
> simply off topic to our filter. To bypass the filter
> you must include
> one of the following words in your message:
>
> sql,query
>
> If you just reply to this message, and include the
> e
At 09:57 04/10/2001 +0800, Osman Omar wrote:
Hi!
>Hi,
>
>I have a table like this
>
>model color
>-
>135 blue
>135 red
>135 green
>235 black
>235 green
>
>
>How do I get how many row in each distinct model
>eg
>
>model 135 have 3 color
>model
Hi!
select count(*), model from tbl group by model;
On 04-Oct-2001 Osman Omar wrote:
> Hi,
>
> I have a table like this
>
> model color
> -
> 135 blue
> 135 red
> 135 green
> 235 black
> 235 green
>
>
> How do I get how many row in each
Hi.
This behaviour is described in http://www.mysql.com/doc/B/u/Bugs.html.
You can circumvent the behaviour with an temporary table for saving
the intermediate result.
Bye,
Benjamin.
On Sun, Sep 30, 2001 at 12:50:37PM +0200, [EMAIL PROTECTED] wrote:
> Hi,
>
>
> I encountered a pro
Already done my friend - works like a dream.
best, Graham
-Original Message-
From: Jeremy Zawodny <[EMAIL PROTECTED]>
To: Graham Nichols <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: 29 July 2001 10:39
Subject: Re: DISTINCT problem
>On Th
On Thu, Jul 26, 2001 at 09:31:29PM +0100, Graham Nichols wrote:
>
> Distinct can only be used with count
>
> count(distinct field_name) --- OK for 3.23 NOT for 3.22
>
> from MYSQL version 3.23. I have only version 3.22. How can I get
> around this please?
Upgrade? :-)
--
Jeremy D. Zawo
X Gogo writes:
> Hi,
>
> I've been using 3.23.37 source tarball compiled for Linux 2.2.19. Now I
> upgraded to 3.23.39 but still the same result... Anyway, it's not that
> important - I was just wondering why SELECT DISTINCT ... LIMIT is slower than
> the
> one without LIMIT. It's not crucial
cation and
does not prevent MySQL from being my favourite cup of database tea :)
Thanx!
George
- èçõîäíî ñúîáùåíèå -
Îò: Sinisa Milivojevic <[EMAIL PROTECTED]>
Òåìà: Re: Re: distinct / limit optimization
>X Gogo writes:
>> Well, the order in which the queries are made do
X Gogo writes:
> Well, the order in which the queries are made doesn't matter. The query with
> LIMIT is always slower than the one
> without it. When I used EXPLAIN SELECT... I actually
> found that MySQL uses temporary table for the query with
> the DISTINCT / LIMIT combination. I presume this
well :)
George
- èçõîäíî ñúîáùåíèå -
Îò: Gerald Clark <[EMAIL PROTECTED]>
Òåìà: Re: distinct / limit optimization
>Because it wasn't in system cache yet?
>
>X Gogo wrote:
>
>> Hi,
>>
>> I've tried the folowing query:
>> SELECT DIS
Because it wasn't in system cache yet?
X Gogo wrote:
> Hi,
>
> I've tried the folowing query:
> SELECT DISTINCT column1 FROM table1 LIMIT 10;
> and got the result for 0.23 sec.
> Then I've tried the same query without limit:
> SELECT DISTINCT column1 FROM table1;
> and got the result fo
the table looking like this:
>>
>> *
>> NAMEEMAILAGE
>> tomtom@mail23
>> petepete@email54
>> davecool@mail21
>> stevesteve@mail17
>> richrich@mail65
>> rich
gt;
> Cheers tom
>
>
> -Original Message-
> From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 26, 2001 5:45 PM
> To: tom harrow; [EMAIL PROTECTED]
> Subject: Re: DISTINCT
>
>
> Hi Tom,
>
> The solution to your problem could be si
IL PROTECTED]]
> Sent: Tuesday, June 26, 2001 5:45 PM
> To: tom harrow; [EMAIL PROTECTED]
> Subject: Re: DISTINCT
>
>
> Hi Tom,
>
> The solution to your problem could be simple if the redundancy is across all
> fields. Then you could simply issue a
>
> CREATE table
dick@mail 33
Cheers tom
-Original Message-
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 5:45 PM
To: tom harrow; [EMAIL PROTECTED]
Subject: Re: DISTINCT
Hi Tom,
The solution to your problem could be simple if the redundancy is
FROM table_duplicate_records
GROUP BY field_1, field_2, field_3;
thanks in advance
:-)
tom
-Original Message-
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 5:45 PM
To: tom harrow; [EMAIL PROTECTED]
Subject: Re: DISTINCT
Hi Tom,
The solution to your
Hi Tom,
The solution to your problem could be simple if the redundancy is across all
fields. Then you could simply issue a
CREATE table distinct_records
SELECT distinct field_1,.field_last
FROM table_duplicate_records
WHERE 1=1;
Or
CREATE table distinct_records
SELECT field_1,.field_la
Your question has been answered already.
http://marc.theaimsgroup.com/?l=mysql&m=99348999221821&w=2
> Hi All,
>
> I've been reading up on how to do joins on the mysql site.
>
> Is there not an inverse function for DISTINCT, so that I can pull out
> duplicate data as opposed to DISTINCT data...o
On 6/25/01 9:10 AM, "anna soobrattee" <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I've been reading up on how to do joins on the mysql site.
>
> Is there not an inverse function for DISTINCT, so that I can pull out
> duplicate data as opposed to DISTINCT data...or will I have to get this
> informa
Hi.
On Tue, Jun 19, 2001 at 09:01:30AM -0700, [EMAIL PROTECTED] wrote:
> I'm generating a pulldown menu using PHP and MySQL. Originally I had the
> list order by model where it put everything in alphabetical order. I need to
> order them by year of model which luckily coincides with the order tha
Have you tried:
select concat("blah", x, "bleh") from y group by x;
> Hi All,
>
> when I use a distinct on varchar fields, does mySQL truncate the value
> of the string to some set value.
> This two queries give me a completely different number of rows and I am
> not sure how to influence the
Try
SELECT name, min(score) FROM grades
GROUP BY name;
--Greg Johnson
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 27, 2001 11:14 AM
To: [EMAIL PROTECTED
Use GROUP BY
SELECT names, score FROM grades GROUP BY names
regards,
-- Andrew
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, March 27, 2001 11:14 AM
Subject: Distinct Selects...
> Q: I have the following table "grades":
>
> +++
select name, score from grades group by name;
Cal
http://www.calevans.com
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 27, 2001 10:14 AM
To: [EMAIL PROTECTED]
Subject: Distinct Selects...
Q: I have the following table "grades":
+
69 matches
Mail list logo
