Database Discussion Boards

Aavesh Agarwal10-Oct-07 1:36

10-Oct-07 1:36

if i m getting you correct so let me tell you there is no such way by which u can get the last row of the tabe u shuld be more specific abut ur query..but there is a way by which u can achieve ur task.

select top 1 * from table
order by somefield

Hope it will work for you.

Thanks and regards
Aavesh Agaerwal

Re: Last value in a Table

Paddy Boyd10-Oct-07 1:56

Paddy Boyd

10-Oct-07 1:56

Last value by what criteria?

Re: Last value in a Table

Paddy Boyd10-Oct-07 2:11

Paddy Boyd

10-Oct-07 2:11

Not sure why you need this bug:

SELECT TOP 1 X
FROM TABLE
ORDER BY X DESC

Re: Last value in a Table

BINOVAR10-Oct-07 2:09

BINOVAR

10-Oct-07 2:09

hi all

Thanks for ur reply..

If I order by I vl not get the last value in the table..

I want the last record in the table .

Re: Last value in a Table

pmarfleet10-Oct-07 3:12

pmarfleet

10-Oct-07 3:12

One of the fundamental principles of a relational database is that the physical order of rows and columns in the tuples (tables) is not important.

http://en.wikipedia.org/wiki/Relational_database[^]

If you need to extract data in a particular order, the data itself should provide the means of doing this. For instance, if you had an numeric EmployeeID field which was incremented every time a new employee joined the company then you could find the last value in your table by selecting the row of data with the highest EmployeeID value. You don't have any data items that you can use for this purpose in your table so you can't achieve this.

Paul Marfleet

Query regarding Trigger

Aavesh Agarwal10-Oct-07 0:37

Aavesh Agarwal

10-Oct-07 0:37

Suppose if i have 4 After triggers and i want to give the order for them in which they fired i can do that with the help of sp_setordertrigger but with this i can only specify which one can be fired first and last one but what abut the rest of triggers how can i order them..please help me..in this i m new for trigger...

Big Grin | :-D

Sucess need one master Stroke Big Grin | :-D

Thanks and Regards
Aavesh Agarwal

Swap the columns values [modified]

Aavesh Agarwal9-Oct-07 23:55

Aavesh Agarwal

9-Oct-07 23:55

How can we swap the values of the Two columns..using query

is it possible or not if it is then how?

Please let me know the query.......

-- modified at 5:26 Wednesday 10th October, 2007

Re: Swap the columns values

Joe10-Oct-07 0:26

Joe

10-Oct-07 0:26

Aavesh Agarwal wrote:
swap the values of the Two columns

Assuming that both the columns are of the same data type
update yourtable set columnA = columnB, columnB = columnA

Re: Swap the columns values

Aavesh Agarwal10-Oct-07 0:28

Aavesh Agarwal

10-Oct-07 0:28

thanks for ur reply..its working but logically how it happens in sqlserver..by using a=b or b=a ..it will not give the same values in the both the columns...

problem in a tipical query(plzzz help me)

Arihant rathi9-Oct-07 21:57

Arihant rathi

9-Oct-07 21:57

hi this is ajay rathi,
i am facing a very big problem,

select month(Start_date) as start_Month, year(Start_date) as start_Year,
sum(case when Status = 'Active' then 1
else 0 end) as Active,
sum(case when Status = 'Inactive' then 1
else 0 end) as Billed
from tbl_Post_Job where Company_Id_Fk = 60 and Start_date between Start_date and Exp_date group by year(Start_date), month(Start_date)order by year(Start_date), month(Start_date)

in this query i want many things,first of all i want to say this is not right query i want query from you..

(1)month(Start_date) as start_Month, year(Start_date) as start_Year
from this line i want month and year from Start_date to Exp_date

(2)in active condition,if any company's status is active from january to april so in this condition the entry of active should be come in january to april in every month

(3)group by year(Start_date), month(Start_date)
in group by section same problem,i want data from Start_date to exp_date

actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...

Date | Active | Billed
------------------------------------
january,2007 | 4 | 2
feburary,2007 | 7 | 5
march,2007 | 4 | 2
april,2007 | 6 | 4

------------------------------------
january,2007 is the start_date and april,2007 is the exp_date

actualy above data january,2007 is coming in form of 1,2007 but i need this data in form of january,2007 so plz tell me how can i convert this data...

plz if any one needs any clarification then plz ask me about this because i need this query immediatly..
plz try to help me as soon as posible

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: problem in a tipical query(plzzz help me)

joemonvarghese9-Oct-07 23:32

joemonvarghese

9-Oct-07 23:32

hi Ajay

Actually i didn't undestand your Qn...

but using the below qry u will get the name of the month..
select datename(month,month(start_date)) from <tablename>

Regards

Joe

Re: problem in a tipical query(plzzz help me)

Arihant rathi10-Oct-07 1:28

Arihant rathi

10-Oct-07 1:28

you gave me solution of one problem "datename(month,month(start_date))"
but this is giving me again and again only "january" nothing else..
so plz give me the other solution..

and wht you could not under stand in my first problem plz tell me....
i need the solution...
plzzzzzzzz

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: problem in a tipical query(plzzz help me)

pmarfleet10-Oct-07 2:55

pmarfleet

10-Oct-07 2:55

Arihant rathi wrote:
plzzzzzzzz

Enough with the textspeak please. It's really annoying.

If you want to get the year/month description, concatenate the result of datename(month,month(start_date)) and year(start_date).

Paul Marfleet

Re: problem in a tipical query(plzzz help me)

joemonvarghese10-Oct-07 4:46

joemonvarghese

10-Oct-07 4:46

sorry for the wrong Qry

correct one is

select DATENAME(month, start_date) from <tablename>

MySQL:(urgent)Why reader.Close() hangs if a LONGBLOG field is selected? [modified]

alesh_nitre9-Oct-07 21:50

alesh_nitre

9-Oct-07 21:50

hi,

I am using MySql .NET Connector 5.1.2.
Following is the code I am using to read a longblob field from a table..

// Select from any table that ***HAS BLOB DATA FIELD***<br />
string selCmd = "SELECT * FROM document";<br />
MySqlCommand allDocsCmd = new MySqlCommand(selCmd, con);<br />
MySqlDataReader reader = allDocsCmd.ExecuteReader();<br />
reader.Close();

The execution hangs at reader.Close();

When I selected everything else but the blob field from the table and it worked fine i.e.
"select id,data from document" hangs but
"select id from document" works ok.
('data' is a LONGBLOB field in document table)

I am searching a cause and fix but lemme know if anyone has ne idea why this is happening..
Thanx.

-- modified at 9:56 Wednesday 10th October, 2007

Column with Null value

Mri1a9-Oct-07 21:28

Mri1a

9-Oct-07 21:28

hi!1
Actually i am a beginner with asp.net and databases .
i want to know that how can i access values from a table where some column name has null value say parentId ..

Mrinal

Re: Column with Null value

Mri1a9-Oct-07 21:40

Mri1a

9-Oct-07 21:40

what if one value is int type and the other is null type

Mrinal

Re: Column with Null value

M LN Rao9-Oct-07 21:47

M LN Rao

9-Oct-07 21:47

SqlDataReader r =//..some code..
int ParentId;
//short form
r.IsDBNull(r.GetOrdinal("ParentId"))?-1:Convert.ToInt32(r["ParentId"]);

OR
int ParentId = -1;
if (!r.IsDbNull(r.GetOrdinal("ParentId")))
{
ParentId = Convert.ToInt32(r["ParentId"]);
}

Intelligence is measured by common sense not by how many scholarly books you read.

Re: Column with Null value

Paddy Boyd10-Oct-07 2:00

Paddy Boyd

10-Oct-07 2:00

SELECT AField FROM ATable WHERE parentId is null

The maximum no. of Stored procedure in a database.

Vipin.d9-Oct-07 20:54

Vipin.d

9-Oct-07 20:54

Hi,
I have a simple doubt.The maximum number of parameters in a stored procedure is 2100.
Like what is the maximum number of stored procedure in a MSSQL database ????

Be very grateful for any advice.

Regards

All I ever wanted is what others have....
CrazySanker

Re: The maximum no. of Stored procedure in a database.

Paul Conrad3-Nov-07 8:52

Paul Conrad

3-Nov-07 8:52

Vipin dev wrote:
The maximum number of parameters in a stored procedure is 2100.

I find that would be hard to reach that number.

Have you looked around google for your question about the max. number of stored procs?

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Concatenating Columns in query

anujose9-Oct-07 20:05

anujose

9-Oct-07 20:05

Hi,

i am using sql server 2005 and i am writing one query to concatename 3 column values..
SELECT distinct(P.firstname + ' ' + P.middlename + ' ' + P.lastname) AS UserName ,P.Id as UserId,G.Id,G.Fkgroupid FROM c_Groupmember G,c_Person P where P.Id=G.FKUserid and G.FKgroupId=" + Id + " and P.Status=0 order by P.firstName asc

it will disply the username if first name and lastname is there .if there is only firstname .then i won't display any value?....

what is the solution for this ?.in query itself i need to get all the usernames.

with regards
anu

Re: Concatenating Columns in query

M LN Rao9-Oct-07 21:51

M LN Rao

9-Oct-07 21:51

Rewrite query as

SELECT distinct( COALESEC(P.firstname,P.firstname,'') + ' ' + COALESEC(P.middlename,P.middlename,'') + ' ' + COALESEC(P.lastname,P.lastname,'')) AS UserName ,P.Id as UserId,G.Id,G.Fkgroupid FROM c_Groupmember G,c_Person P where P.Id=G.FKUserid and G.FKgroupId=" + Id + " and P.Status=0 order by P.firstName asc

for more information see COALESCE function documentation.

Intelligence is measured by common sense not by how many scholarly books you read.

Re: Concatenating Columns in query

anujose9-Oct-07 22:28

anujose

9-Oct-07 22:28

it is showing an error

Msg 195, Level 15, State 10, Line 1
'COALESEC' is not a recognized built-in function name.

Re: Concatenating Columns in query

anujose9-Oct-07 22:44

anujose

9-Oct-07 22:44

ya.i got .it is 'COALESCE'. Not 'COALESEC'

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.