|
OK, but you select the max date and not the max date per type
I have several types and I shearch the last value for each type
and not the max date per type
modified 26-Oct-11 10:39am.
|
|
|
|
|
MikeDhaan wrote: and not the max date per type
The inner select has a group by type which should produce a list of MAX(dates).
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Correct but the result with the extract below is not correct.
MAX(TheDate), Type, Value
2011/08/11 , T1 , 10
2011/08/17 , T1 , 12
2011/09/25 , T1 , 11
2011/08/11 , T2 , 15
2011/10/10 , T3 , 15
...
The result is :
MAX(TheDate), Type, Value
2011/08/11 , T1 , 10
2011/09/25 , T1 , 11
2011/08/11 , T2 , 15
2011/08/15 , T2 , 10
...
Because
select MAX(TheDate) from Table group by Type
return
2011/09/25 (for T1)
2011/08/11 (for T2)
2011/10/10 (for T3)
|
|
|
|
|
Do a little research on Row_Number, Partition and Ranking. Using these you can set up an inner query that ranks the records by date partitioned over type.
The outer query would then join by date and type where the rank = 1.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Since there are multiple values the same in TheDate column it can not be used as a key and that is why you are getting those results. Another, more complicated way, is to use the date and type as a key. Try this
select date
,type
,value
from table t
, ( select max(date) max_date
,type type
from Table
group by type
) tg
where t.date = tg.max_date
and t.type = tg.type
order by type
;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Super,
It's now in order !
Thanks
|
|
|
|
|
I'm always reluctant to push the partition/rank solution as someone always seems to come up with a simpler answer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A partition/rank solution wouldn't need the join and therefore probably performs better.
The simplest answer isn't always the best.
|
|
|
|
|
SELECT MAX(TheDate), Type, Value FROM MyTable GROUP BY Type, Value
|
|
|
|
|
Hi,
I had mysql installed on my laptop (Windows Vista) but my operating system got corrupted and i had to remove the hard disk and use it as external hard disk.
so currently i have access to the laptop when i connect as external hard disk and the I can go to the Program Files\Mysql folder just like any folder in my external hard.
Now I want to know how can I restore-re-create the database on my new machine using the files of the mysql available on my external hard disk (which use to be my laptop hard disk)?
Thanks,
Jassim
|
|
|
|
|
Change my.ini to point to the correct datadir and restart MySql.
Where's your backup?
Precisely, and that's why you need one. Store it on a USB-key and keep it under your pillow when you go to sleep
Bastard Programmer from Hell
|
|
|
|
|
I know! it's a big mistake not to take a backup huhuhu
listen....
I tried but mysql services was failing to start..
datadir="F:/ProgramData/MySQL/MySQL Server 5.5/Data/"
then I tried putting it in a folder within the new machine with no2 like this:
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data2/"
and still the same problem..
then I tried to copy the folders for the dbxxx (my database) in the C:/ProgramData/MySQL/MySQL Server 5.5/Data and I got it in the list but i am getting:
1146 Table dosen't exist for every table when i try to open it..
what should I do now?
|
|
|
|
|
this is the error when I tried to start mysql services in the first scenario..
The MySQL service terminated unexpectedly.
|
|
|
|
|
Sounds like the database-file is corrupt. Did it die during a write?
jrahma wrote: what should I do now?
Google for "repair corrupt MySQL" and try everything you find.
Bastard Programmer from Hell
|
|
|
|
|
no it dies during laptop booting only even before windows starts its booting
|
|
|
|
|
Hi,
I have a function like:
if(@a>1)
begin
select * from t1
join t2 on t1.id=t2.id
else
select * from t1
end
this is simple but when I have many situation and more tables to join it grows a lot. I'm looking for a solution that let me depend on the situations make my select query with or without joins.
All the bests,
Agh
|
|
|
|
|
here is the way how to build dynamic query in T-SQL
declare @query as nvarchar(max)
set @query = 'select * from mytable '
if(@ParameterValueIS = 1)
begin
set @query = @query +' where id=1'
end
if(@ParameterValueIS = 2)
begin
set @query = @query +' where id=2'
end
exec (@query)
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
mehrdadov wrote: I'm looking for a solution that let me depend on the situations make my select query with or without joins.
Let me rephrase that, to make sure I understood you correctly; you're looking for something simpeler than a join-statement?
Bastard Programmer from Hell
|
|
|
|
|
mehrdadov wrote: this is simple but when I have many situation and more tables to join it grows a
lot.
Why are you doing this?
As noted in the other reply one can create a query dynamically. A client app could do this as well. But you really shouldn't have "many" of these. It should normally be limited to a very few cases.
|
|
|
|
|
Good Day All
i have the Following string in a Field
'This
is the
reason that i did this>> '
i want to remove spaces in TSQL to this
'This is the reason that i did this>>'
THanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
here it is
declare @NewLine char(2)
set @NewLine=char(13)+char(10)
select rtrim(ltrim(Replace('This
is the
reason that i did this>> ',@NewLine,'')))
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You are a Star thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
You are welcome.
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
That might not be what you want if the string is 'hello\r\nworld'.
|
|
|
|
|
I wrote a CLR function to do that, compresses whitespace to a single SPACE:
namespace PIEBALD.Lib.LibExt.Compress
{
public static partial class LibExt
{
public static string
Compress
(
this string Victim
)
{
System.Text.StringBuilder result = new System.Text.StringBuilder ( Victim.Length ) ;
bool first = true ;
for ( int i = 0 ; i < Victim.Length ; i++ )
{
if ( !System.Char.IsWhiteSpace ( Victim [ i ] ) )
{
result.Append ( Victim [ i ] ) ;
first = true ;
}
else if ( first )
{
result.Append ( ' ' ) ;
first = false ;
}
}
return ( result.ToString() ) ;
}
}
}
CREATE ASSEMBLY Library FROM 'C:\bin\Library.Sql.dll'
GO
CREATE FUNCTION dbo.Compress(@Source NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME Library.[PIEBALD.Lib.LibExt.Compress.LibExt].Compress;
GO
|
|
|
|