|
1)In SQL Server:
select top 1 * from (select top 6 * from employee order by salary desc) as s order by salary asc 2)There are a few ways, but I like this one:
select * from (select count(*) as Duplicates, * from Employee group by name,salary) as tbl where Duplicates > 1 (but you have to have every field in the group by clause)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I had a Question I was hoping you might be able to help me with.
I am using VB.NET 2005 express and I have a list box that is bound to a table in a dataset. I want to be able to select one of the items in the list box and then use an item that corresponds to this item in the list box and I do not know how to do this.
For example, if I had a table named Person and this table had two fields one named PersonID (which is the key field), PersonName, and PersonAge. Say I had PersonName bound to the list box. So say I click on one of the PersonNames in the list box and then wanted to use the PersonAge, which corresponded to the name I had clicked, in a calculation. How would I access the PersonAge value?
I appreciate any help you could give me.
Thanks,
|
|
|
|
|
Check this example
http://www.thescripts.com/forum/threadnav348524-2-10.html
|
|
|
|
|
hey all!
i was wondering can any 1 give me a code sample in c++(if possible dono if is or not) for connecting to a data base. i have used hem before in php but have no idea how to do it in c++
thanx, jt
|
|
|
|
|
This is a very open question...
What type of database???
If you just need the connection string check our http://www.connectionstrings.com
Also do you want to perform inline SQL / Procedures or use the VS ADO database objects...
Not much help but not much to go on...
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
Hi, please help
I wanna select top 5 from table satisfying a condition
and at the same time count the number of rows returned based on selection.
Select top 5 LastName, (Select count (*) from TBL where lastname = 'Cruz') as Count from TBL where lastname = 'Cruz'
assuming there are only 3 rows with Cruz as latname
result should be:
Lastname | Count
Cruz 3
Cruz 3
Cruz 3
I mean my query doesn't work and have researched over the internet.
Please help. thank you.
Gerri
|
|
|
|
|
Select top 5 LastName, count (*) as [Count] from TBL where lastname = 'Cruz'<br />
group by lastname order by [Count] Desc
|
|
|
|
|
it doesn't work, seriously. I already copy pasted and been doing some workarounds on this. using sql 2005. or what's wrong really?
thank u
Gerri
|
|
|
|
|
Select top 5 LastName, count (*) as [Count] from TBL where lastname = 'Cruz'<br />
group by [Count]
|
|
|
|
|
CandyMe wrote: Select top 5 LastName, (Select count (*) from TBL where lastname = 'Cruz') as Count from TBL where lastname = 'Cruz'
If this isn't working, there is some other problem.
CandyMe wrote: I mean my query doesn't work and have researched over the internet.
Can you be a bit more specific as to what error message you are getting. Maybe give us some samples of the data. According to what you have showed us here, there is absolutely NO reason your query should'nt return what you expect.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I mean my query doesn't produce the result I'm expecting.
For instance, I selected Top 5 with a given condition. But 3000 passed this condition. Rowcount should only be 5.
Upon querying such
Select top 5 LastName, <br />
(Select count (*) from TBL where lastname = 'Cruz') as Count <br />
from TBL where lastname = 'Cruz'
it gives me: the top 5. but doesn't give me the correct count: 5
LastName | Count
Cruz | 3000
Cruz | 3000
Cruz | 3000
Cruz | 3000
Cruz | 3000
Thank u.
Gerri
|
|
|
|
|
I figured it some 2 hours after my last reply.
I decided to create a temp table where I'd do my select top
then just count the number of rows in that temp table
with the whereclause, ofcourse
CREATE TABLE #RowCount(SubscriberId INT)
EXEC ('INSERT #RowCount' + ' SELECT TOP ' + @Top + ' (SubscriberId) FROM ' + @TableName)
--get total pages
EXEC ('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM #RowCount')
--get total number of rows
EXEC ('SELECT COUNT(*) FROM #RowCount')
so if i selected top 10000 and only 3000 passed the condition
totalrowcount is 3000; otherwise, if i selected top 2500 and 3000 passed the condition, totalrowcount is 2500
thanks!
Gerri
|
|
|
|
|
Or you can try something like this:
SELECT
sub.surname,count(*) as count
FROM
(select top 5 surname from myTable) sub
GROUP BY sub.surname
--[WHERE surname = 'CRUZ']
OUTPUT will bev [without the where clause]
CRUZ 3
TOM 1
JACK 1
if you want to see all 5 rows [basically repeating ]
SELECT sub3.surname,sub2.count
FROM
(select count(*) as count from
(select top 5 surname from myTable where surname = 'CRUZ') sub) sub2
CROSS JOIN
(select top 5 column_name from myTable where surname = 'CRUZ' ) sub3
|
|
|
|
|
thanks so much. well appreciated.
Gerri
|
|
|
|
|
how can i get the time and date in the datecreated field in a data table while inserting a record?
|
|
|
|
|
You can return it as an OUT parameter if you are using a stored procedure, or do a select query on the record after you've inserted it. AFAIK there's no easier way to get this.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|
|
When you insert into the table, use getdate() as the value for the DateCreated field:
insert into mytesttable (MyDateField) values (getdate())
Or, to make a default value of the current datetime, in design view in the Enterprise Manager type (getdate()) in the Formula field for the table.
Or, when you create the table in the Query Analyzer, do it this way:
CREATE TABLE [MyTestTable] (
[IDColumn] [int] IDENTITY (1, 1) NOT NULL ,
[DateCreated] AS (getdate())
) ON [PRIMARY]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Ok, I know how to start the query designer in VS2005 and I can make a query and run it and see the data, thats all good.
I can also create new stored procedures by using the server explorer to go the SP folder, right click, add new. I then type in my stored procedure and everything works fine.
However, is there a way to create stored procedures with the query builder, other than using typed datasets?
|
|
|
|
|
hi all
every time i add a column to my table with the type text or ntext all what is entered in the database is only the first 16 characters. it happened also with all my colleagues
plz help me with this problem
abdelhameed81
|
|
|
|
|
How is the column defined?
How are you inserting text into the column?
Mark
|
|
|
|
|
the column is created in the design view of the SQL enterprise manager and the data is inserted to it from an ASP.Net application
abdelhameed81
|
|
|
|
|
What is the max length of the text for the column?
I meant can you post code snippet showing how you do the insert?
Database is SQL Server?
Mark
|
|
|
|
|
FWIW text is a datatype that will be deprecated in future versions of SQL Server. Keep that in mind as you develop your tables. varchar or nvarchar are the best choice in my nosey opinion.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
|
|
|
|
|
Hello,
I have a couple questions and would really appreciate your responses.
Question:
1. If you are new to a database, is there a way that you can quickly find out relationships between the tables?
2. I need to retrieve information on all the tables in a database to find out which tables have a rowcount of more than 50,000 records then delete some record which thus reducing the table to 50,000 rows so as to make space. Is there a way to do that? and if so, what is the syntax to obtain this?
3. How can I cascade constraints when deleting records from a specific table?
I am using SQL server 2000 to accomplish this task.
Thanks
Greg
Coding makes the world go round!!!
|
|
|
|
|
Skanless wrote: 1. If you are new to a database, is there a way that you can quickly find out relationships between the tables?
There are diagrams that you can create to see the relationship between the tables.
Skanless wrote: 2. I need to retrieve information on all the tables in a database to find out which tables have a rowcount of more than 50,000 records then delete some record which thus reducing the table to 50,000 rows so as to make space. Is there a way to do that? and if so, what is the syntax to obtain this?
You can use the sysobjects table that can return the tables existing in your database, then query each table to check how many records it has and delete the ones over 50,000. I can't remember the correct syntax, I'm sure a bit of googling will do.
Skanless wrote: 3. How can I cascade constraints when deleting records from a specific table?
You need to specify the necessary constraints to the tables before deleting the record, and it will automatically trigger the deletion of child records.
"A democracy is nothing more than mob rule, where fifty-one percent of the people may take away the rights of the other forty-nine." - Thomas Jefferson
"Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." - Benjamin Franklin
Edbert
Sydney, Australia
|
|
|
|