|
should care the order in every group
for example,
table:
id data num
1 2008-1-1 2
2 2008-2-2 2
3 2008-1-1 1
4 2008-2-2 4
5 2008-1-1 5
the result will be:
data first last
2008-1-1 2 5
2008-2-2 2 4
Here in every group take first/last in the order of id asc.
system
|
|
|
|
|
select distinct data,<br />
(select top 1 (t1.num) from myTable as t1 where t1.data = myTable.data order by t1.id ) as firstRow,<br />
(select top 1 (t2.num) from myTable as t2 where t2.data = myTable.data order by t2.id desc) as LastRow<br />
from myTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I got a problem because myTable is a sub-query, so I can't have 2 alias names.
How to deal with it?
Thanks.
system
|
|
|
|
|
What is your real table name?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
select top 1 (t2.num) from myTable as t2 where t2.data = myTable.data order by t2.id desc
Now I want to consider the record whose ID is above 10, so I shall use
(SELECT * FROM myTable WHERE ID>10)
to replace myTable in the above SQL statement.
But lack a alias name, how to write it?
system
|
|
|
|
|
Here is the table for query:
date(PK) value
2008-1-1 5
2008-2-1 10
...
Now sort the table by date ascendingly,
and make every 10 record a group
(the last group may catain less than 10 records),
I want to query last record in every group.
system
|
|
|
|
|
hi, i need to create a stored procedure with a Count(*) option and also an output parameter with the Id of the selected record. IS it possible?
|
|
|
|
|
Yes
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Well the question does not make sense:
ID infers a unique identifier
Count(*) requires a group by
So your question is can I group by a unique identifier. Try rephrasing the question (I think you just got it wrong) and give us some more detail.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why do you use Count(*), it is better to use Count(ID)
ID infers a unique identifier in that table
CREATE PROCEDURE titles_count @@TITLE varchar(40) = '%', @@count int OUTPUT
AS
SELECT @@count = count(title_id)
FROM titles
WHERE title LIKE @@TITLE
GO
Venky
|
|
|
|
|
Why is it better to use count(field) instead of count(*), I have never heard that there is a difference in cost
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to import data to Microsoft Sql Server 2005 from .csv file
In .csv file I am having a column which has numeric data . During the import process I am receiving data conversion error.
In case I used this column as string then it works, but I want column as numeric.
Kindly tell if I am missing something.
Thanks in Advanced.
|
|
|
|
|
a_b111 wrote: which has numeric data . During the import process I am receiving data conversion error
Either you have a non-numeric value, or your numeric data type does not match that of your file. Check all your values against your data types.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
As Bob said - data types, probably a blank cell that is treated as "".
I always import into a staging table of varchars and use a stored proc to do the transforms AFTER loading the data. Dates will also screw you with weird formats.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have tried to import into a staging table of varchar and use a query to do the transforms AFTER loading the data, but it failed.
Please tell me the stored procedure to do the transforms AFTER loading the data.
|
|
|
|
|
a_b111 wrote: Please tell me the stored procedure to do the transforms AFTER loading the data.
You have to write it, in your OP you said you could get it to work using string - varchar so that part should work.
Now you have the data in front of you and you know where it has to go - so write the procedure to do the work. This removes the LOAD from the potential problems you can have, now all you need to do is transform the data into your tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
a_b111 wrote: Kindly tell if I am missing something.
Yes. 
|
|
|
|
|
hi frnds,
I need to transfer data from a single source column to multiple destination columns.
Sample data:
SOURCE TABLE:
F1 F2 F3 <----header
A 234 D11 D12 D13
B 235 D21 D22 D23
DESTINATION TABLE:
col1 col2 col3 col4
A 234 F1 D11
A 234 F2 D12
A 234 F3 D13
B 235 F1 D21
b 235 F2 D22
B 235 F3 D23
Is it possible through activex scripts?? If Yes, how??
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
Hi,
for each tuple in source table you have to create 3 instances in destination table,
you can do this via stored procedure, by help of various methods of choice.
take temp table with columns [SrNo numeric identity(1,1)] and four column of source table.
transfer source table into temp table
take max SrNo in a variable say count
for each row of temp table from 0 to count
BEGIN
insert destination table([source col1],[source col2],F1,[source col3]
insert destination table([source col1],[source col2],F2,[source col4]
insert destination table([source col1],[source col2],F3,[source col5]
END
I think this way will transfer all your record from source table to destination table in desired manner.
Bi
|
|
|
|
|
Hello,
I installed sql server 2005 and i have sql configuration manager.so please tell me how to access it to create database and tables ,so that i can work on .net
Thanks
|
|
|
|
|
|
I have sql server configuration manager.when i click on that
sql server 2005 services
sql server 2005 network configuration
sql native client configuration
I don't have management studio.
I tried to load again it's giving warning:
The current system does not meet the recommended hardware requirements for this SQL Server release. For detailed hardware and software requirements, see the readme file or SQL Server Books Online.
I saw it requires 512MB of RAM and i have 256MB
Thanks
|
|
|
|
|
mkalantri wrote: I saw it requires 512MB of RAM and i have 256MB
There's your problem. If you have so little RAM, you're not really going to be able to accomplish much with a database. You really need to beef up your system first.
|
|
|
|
|
mkalantri wrote: I saw it requires 512MB of RAM and i have 256MB
Well, there you go. 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Based upon your question it is appearent you have a ways to go before you need to concern yourself with .Net and the CLR.
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
